cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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?

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
artemus Member
Member

Re: Is combining steps better for 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.

View solution in original post

trebgatte Established Member
Established Member

Re: Is combining steps better for performance?

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

View solution in original post

6 REPLIES 6
artemus Member
Member

Re: Is combining steps better for 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.

View solution in original post

trebgatte Established Member
Established Member

Re: Is combining steps better for performance?

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

View solution in original post

artemus Member
Member

Re: Is combining steps better for performance?

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]

Re: Is combining steps better for performance?

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 Member
Member

Re: Is combining steps better for performance?

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.

Highlighted

Re: Is combining steps better for performance?

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.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (5,357)