Is combining steps better for performance?

[richard-powerbi]

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?

[artemus · Accepted solution]

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.

[trebgatte · Accepted solution]

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

[artemus]

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]

[richard-powerbi]

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.

[artemus]

The issue I have is that it is grayed out from the start, but query folding is happening.

This happens with the Azure Data Explorer data source. Also, if anyone knows how to update a connector to show the "View Native Query" let me know.

[richard-powerbi]

Don't know about that. But I know the cute native query code feature isn't that old. Maybe not all sources support this yet and it will be added in the future. Probably better to start a new topic for your question.

[artemus · Accepted solution]

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.