Let Power Query write your SQL for you!
Vložit
- čas přidán 24. 07. 2024
- We tell you to push your transformations upstream as much as possible. But you don't know how to write SQL. That's ok, let Power Query do it for you to move your project forward. Patrick shows you how!
📢 Become a member: guyinacu.be/membership
*******************
Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.
🎓 Guy in a Cube courses: guyinacu.be/courses
*******************
LET'S CONNECT!
*******************
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
🛠 Check out my Tools page - guyinacube.com/tools/
#PowerBI #GuyInACube - Věda a technologie
My company is using Paginated reports through Power BI Report Builder. I have been creating "proto-types" in Power BI to show end users before I build the whole thing as a Paginated report - this 6-minute video will have me days --- if not weeks of work - Thanks Patrick!!!!😁
lookout DBAs!....Patrick is giving away secrets
This is super cool. I write queries directly from the server, but this will definitely help with future projects! Thank you!
Patrick!!! I spend my whole afternoon creating views I didnt know I can create them this fast!!!!game changer this one
wow amazing. Never thought about. Thank you for sharing and kudos to the guy who mentioned it
That's really handy, to be able to translate all the logic into SQL, that's amazing, thank you for this post Patrick
This is great!!! Thanks Patric.
Amazing how this has been under the hood of Excel for decades in other forms. However , its nice to see another generation learn from this.
Nailed it! Thanks man!
Wow this is really cool stuff. Thanks for sharing.
This such a cool feature! And think about the posibilities to use Power BI for development and thereafter push it more upstream in the BI-model for the company!
Woah! 🤯Super cool trick. Thanx!
Perfect as short to quick learne 🎉
Excellent Patrik, Well Done, You are a genious Teacher
Khalid Khan, Islamabad, Pakistan
Is there a reverse version of this, that could take a SQL query and turn it into M? Because that would be extremely helpful for getting folks on my team's heads wrapped around working in M.
Hi it's cool , i just can't see the last column when my table should be listed it's relationship
Awesome tricks ....
Thanks!
Beautiful Bro 👍🏿
Now I will use this after all my Transformations in PQ and create views for the entire data model. I will now only have 1 query in PQ steps, which will be "Source"
Yeheeeey...... Thank you sooooooo Muuuuccchhhhhhhh
Very Nice.
If the query is already folding, is there still a requirement to create a separate view?
Hi Patrick I am currently using sql views but if I need to create a lot of sql views say 5/6 per report that can bloat up my view folder really quickly can you provide any organisation/management advice would really appreciate it. Using Oracle sql developer
Super cool :)
can you send me the link to the data I follow through?
Sheesh… that’s efficient
Hi Patrick. Please, help me. PBI don't load data. I see the message (Load. Evaluation)
Power query replaces sql. You can create all your queries quicker with gui rather then writing a code.
Lets upstream!
u the best 💯💯💯💯💯💯💯💯💯💯💯💯💯
I've been doing this for years with PQ in Excel. And after a while realized it wasn't worth the effort to convince DBAs to create views when there is no performance benefit for me. Different if you get a good DBA who will create a materialized view and perf tune, but in general those DBAs are a rare breed.
Hahaha brilliant ❤
A colleague of mine criticizes such SQL code. He says it's suboptimal
Does this mean that Power BI Is just a "wrapper" for SQL Server :)
on my way!! to befriend DBA for all my power query ETL😂😂😂😂
What would be a reason why the last magic column would not show in Power Query? Thank you!
I found it!
@@tashagrihma6052 how did you find it, I've just quickly tried import and direct query mode and it doesn't show. Is there a setting i need to enable?
@@joebateman9668 I was originally adding in the table or sql statement Under the Advanced options area. Instead of adding it there, I only entered the server and DB name to bring in all the tables. From there I selected the table I wanted and then it brought in the relationship column. Hope that makes sense!
@@tashagrihma6052 that's what I tried as well and still nothing. Having said that, while I'm pretty new to SQL I don't think our DW properly follows best practice with defined primary and foreign keys. Don't know if that would make a difference.
Isn't it amazing that in every Power BI job description they require SQL skills that are not really necessary with Power BI :)
knowing sql is still really important for a power bi job as with personal experience I at one point used to use power query but what I found was that it can get really complex esp when doing merges and appends and referencing at times for quite long periods was just staring at the power query screen loading so had to go back to using sql, but maybe in the future power query might become a better option than sql.
@@amarkhaliq5020 I try to avoid merges and appends. I also often split huge multi-column tables into smaller ones trying to normalize them as much as possible. Then all the stuff I do in DAX.
@@1yyymmmddd Yeh that should work as well, I've not tried it myself but I am sure you can clean up the data and remodel it using dax.
Roches’s Maxim strikes again
This is just to show, which sql query Power query is sending to the data source, there is no meaning of doing this extra activity.
if it is already folding, it simply means it wont import all those unwanted data when you will refresh your dataset.
the real question is about those transformations steps, which can't be part of query folding, and that should be requested to be done from backend
Hi Patrick, from a performance standpoint, shouldn't that be exactly the same? Why even bother then... Not lazy, just really efficient 😅
Yes, I had the same question, why creating a view ?
The data will refresh quickly and less import data size I guess if the data directly comes from upstream right?
The data will refresh quickly and less import data size I guess if the data directly comes from upstream right?
@@desivlogs4374 Nope. Doesnt matter, a view isnt faster the a SQL query.
@@MDevion but the data whole data would not be imported into powerbi ...just the data which is being fetched by the view, like the clean up will be done at the source only making it faster than actually using the power query functions..right?
It's a nice feature, but doesn't really solve any problems. Small queries like this are written in seconds and way more cleanly by someone who knows a bit of SQL.
And if you do have very complex power queries, they simply will stop performing at some point and you will need to create some etl process in you source or DWH.
I appreciate sharing knowledge, but people need to be warned and taught when to use it and when not. I have fixed too many DWH and code of analysts that didn't know what they were doing.
But to be fair to the analysts I have seen developers making bigger sins.(Like a relational model in PowerBI with cross directional filtering and wondering why it performs like poo)
🤣🤣 All these tools Power, this and Power that only make sense if your data from the source is messed up to start with.
If an organisation is using excel, access, rdbms, even text files to manage data storage, it is always better to do things correctly from genesis when planning following standards ERD model.
Most of these data in some of these videos would not even have needed transformation if the data model was taken into consideration from the begining before data is inputed into cells etc.
Many people just open excel and start typing without actually think what the data should even look like and then after 10,000 rows we start looking for data transformations. A good data planning, and reverse engineering would sort these problems once and for all
All that would be needed is SQL Query and you can get all your report, charts, graphs etc. But good insight nevertheless
If the DBA, doesn't reformat that query before turning it into a view, he or she probably isn't a DBA...
Not so sure about that. Once you apply a few steps in PowerQuery, the generated SQL is absolutely horrendous 😂
Movies are too fast, beginners may have a problem with understanding ..
By watching this, I realized how far I am from understanding this tool.. this sounds like Chinese to me.. I understood none of it 🤦🏻♀️
Мардат виро
that is ridiculous
My company is using Paginated reports through Power BI Report Builder. I have been creating "proto-types" in Power BI to show end users before I build the whole thing as a Paginated report - this 6-minute video will have me days --- if not weeks of work - Thanks Patrick!!!!😁
Are you converting the SSRS reports to paginated reports? What resources are you using for parameters to work smoothly?
@@desivlogs4374 yes, I am putting filters in my query as some of the parms (prior month, etc.) and hard coding others like vendor name = xxxx. These are typically static type reports and data files (xml/csv) that run monthly.