Forum Discussion
Is combining steps better for performance?
Is it better for performance to combine steps?
Let's say, is below better for performance than the separate steps usually created when going trough the UI?
= Table.SelectColumns(Excel.Workbook(Source{[Name="ExcelFile.xlsx"]}[Content]){[Item="ExcelTable",Kind="Table"]}[Data], {"Column1", "Column2", "Column3", "Column4", "Column5"})Of course, it lacks readability, but after having designed something, would it be good practice to combine some steps?
Is my assumption correct, that with each new step a 'new reality' is loaded correct? And thus with reducing steps it would increase performance?
No, the mode of loading each step only occurs in query editor design mode.
However, depending on what your datasource is, it may be better to mizimize the number of tables you load directly into the model, and use DAX to create additional tables as needed to do filtering on.
No it doesn't. The only thing to be aware of is whether you are putting your query folding actions at the correct position in the flow.
Combining steps also makes maintenance harder. We wrote an article on some recommendations to make your M code more maintainable. You can see it here: https://marqueeinsights.com/tip-how-to-make-your-power-query-m-code-self-documenting/
I hope this helps!
--Treb
6 Replies
No, the mode of loading each step only occurs in query editor design mode.
However, depending on what your datasource is, it may be better to mizimize the number of tables you load directly into the model, and use DAX to create additional tables as needed to do filtering on.
No it doesn't. The only thing to be aware of is whether you are putting your query folding actions at the correct position in the flow.
Combining steps also makes maintenance harder. We wrote an article on some recommendations to make your M code more maintainable. You can see it here: https://marqueeinsights.com/tip-how-to-make-your-power-query-m-code-self-documenting/
I hope this helps!
--Treb
Is there a good way to determine if a query is folding (assuming you aren't using a datasource that supports View Native Query)? The only method I have found so far is by adding a step: Value.Metadata(PreviousStep)[QueryFolding]
- Yes, right click on a step and see if 'view native query' is not greyed out. When you can click it, and view the SQL code all is good. Try to do as many steps in the right order to keep this from being greyed out. Huge performance gain in my experience.
