How we optimize Power Query and Power BI Dataflows

Let's go into Adam's mind as he optimizes Power Query to make some Power BI Dataflows run faster to avoid resource usage. The result is INSANE AMAZING!

Power Query M function reference


*******************

Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.


*******************
LET'S CONNECT!
*******************


***Gear***

#PowerBI #PowerQuery #GuyInACube

[GuyInACube]

One thing not mentioned in the video is to be aware and careful about SQL Injection.

[ItsNotAboutTheCell]

M-ind bending! Love the Advanced Editor and some neat tricks in here too for those performance folks! Keep up the PQ videos! One happy CAT!😻😻😻

[daryllynch]

Hi Adam, thanks for the Video. I use this approach all the time. One thing that I don slightly different is to use the List.Buffer function because sometimes, I want PQ to read the List only once to get the full Set. I find it sometimes help performance. One suggestion for each time is consider Folding Query scenario. In this situation the following statement works:

#"Filter Rows" = Table.SelectRows( Source, each List.Contains( CurrencyList, [Source Column] ) )

This leads to folder query in most data sources. I also feel this can be more effective T-SQL because the filter is applied to the main table without the need to the Left or Inner Join.

[danmeiss]

Adam's version of "unplugged". See the thought process behind problem solving...

[billcoleman]

Nice. You could probably hook straight to the table, use selectcolumns() & filter steps and let query folding do its thing to keep it clean for incremental refresh etc - that should work, right?

[MrSparkefrostie]

For anyone running into issues where your list converts to a table, this should help if your query is SQL, will need some tweaks if you use a differrent source. I am guessing it changes to table as its a live source and not sitting with load disabled

Text.Combine(Sql.Database("Server", "Database", [Query = "SELECT CAST([NeededColumn] as VARCHAR) AS NeededColumn
FROM [dbo].[NeededTable]
WHERE Conditions = Conditions
"])[NeededColumn], ", ")

[JCMCodaste]

This is a great video! thank you, Adam. Good to see that you guys are playing more with dataflows now! I will take it as a good sign of their "health" within the Power BI roadmap! (at my own risk ;)
It would be great to have more videos on how to optimize dataflows deployment when linking them across different workspaces (also where the latter are engaged in a deployment pipeline!) ..choosing between the two different connectors in those specific situations is still making me scratch my head, to be honest. Happy 2022!

[DanielWeikert]

Where can I see which operation is "expensive" and should be avoided? Is there a reference doc for that? Thanks

[sidharh]

Hey Adam, I was just wondering, if you could possibly make a video regarding Significance Testing in Power Bi?

[mikemagill]

Q. Awesome video. This is exactly the challenge I’m facing currently but m pulling in a SQL table with c. 54. million rows and then need to eliminate rows based on an external table with c. 10, 000 rows. I totally get what you’ve done but can you inject a SQL ‘WHERE’ command with a comma separated list of 10, 000 values? Is there an upper limit?

[fsanfo]

I have this query that takes 20m to run in my Oracle PL/SQL client. And I discovered that PQuery Online evaluates querys for 10 minutes only. So what do you recommend me? Retrieve the data by chunks or something else? I'm Pro User using Power BI service.

[pabeader]

Added this to my BI Goodies playlist. Good job and good timing for me. Have a project where this is going to come in handy. Not so much for the performance gain as an easy way to get excel into PQ.

[bbangel]

I have a Power App Dataflows which transforms and load data to a Dataverse table . This operation now takes around 50 minutes to complete, and I am speculating that the issue is not in the calculation in Power Query, but rather in the loading operation to the Dataverse table. How can I go about optimizing/improving the data loading performance?

[Kierslee]

Adam, where can I find the details regarding the time and resources to run the refresh (cpu, memory, etc.)?

[fluffigverbimmelt]

Nice, I might be able to use this on a db where DBA only let's us access via NOLOCK hint (yeah, don't get me started on that)

[gravestoner]

All my data comes in excel sheets, i have 1 sheet per month for 2 different data fact sheets. Is there a good way to combine these automatically without merging?

[MrSparkefrostie]

What I am looking for is to apply predicates against an upstream dataflow, maybe it does this already, time will tell

[Phoenixspin]

The thumbnail makes me think of monkey brains. (Indiana Jones reference)

[matthiask]

This almost like in Inception. Just with a list.

[remyschrader]

In Advanced Editor: ‘PromotionKeysAsList = Promotion[PromotionKey],
CurrencyKeysAsList = Currency[CurrencyKey], ’
Values from a column in a table return as a list from ‘NeedValuesAsListType = TableName[ColumnName]’