cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
justlogmein
Helper II
Helper II

Why is my query taking so long to run?!

I have extracted a problem query that is a part of a much more complex model. This query is giving me hell because it is taking several hours to run and I don't understand why. I have a few filters and merges in there, but my source data is not millions of rows and I feel my steps are fairly basic that it shouldn't be taking this long. It's at a stage now where I am trying to code for all the steps in Python because PQ just isn't useable like this. Past problems in PQ that were taking hours I have been able to recreate in Python (after a lot of work coding) which take just seconds to run, though I don't want to have to do this everytime PQ decides it doesn't want to play ball.

 

I was hoping someone could take a look for me and show me how I can improve the time. I have included the link below (it's in Excel becuse I need the reference tables, but the PQ is all the same as Power BI).

 

https://jmservicescomau-my.sharepoint.com/:x:/g/personal/jacob_jmservices_com_au/Ef-O6sFaKa9DkBXjdJc...

 

I have also just listed this task on Upwork if anyone is interested in earning money to come up with a much faster solution - paying up to 150USD for the right person. https://www.upwork.com/ab/applicants/1486954907972657152/suggested

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

Hi @justlogmein ,

 

The "I have a few filters and merges in there" bit is where I would start. Merges are very expensive in Power Query if they can't be folded back to the source system.

 

Initial options:

 

1) If your data source can't accept folded queries, then get rid of as many of the merges as possible and instead replicate them within the data model with relationships in a STAR SCHEMA format.

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema 

 

2) If your data sources will accept folding (SQL Server etc.) then right click on the final step of EVERY query that is involved in a merge operation and check whether that query is being folded back to the source. You're looking for 'View Native Query' to be selectable and not greyed out:

BA_Pete_0-1643359506917.png

 

Either way, you should really be limiting PQ merges and aiming for a STAR/SNOWFLAKE relational schema in your data model instead.

 

Pete

View solution in original post

7 REPLIES 7
BA_Pete
Super User
Super User

Hi @justlogmein ,

 

The "I have a few filters and merges in there" bit is where I would start. Merges are very expensive in Power Query if they can't be folded back to the source system.

 

Initial options:

 

1) If your data source can't accept folded queries, then get rid of as many of the merges as possible and instead replicate them within the data model with relationships in a STAR SCHEMA format.

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema 

 

2) If your data sources will accept folding (SQL Server etc.) then right click on the final step of EVERY query that is involved in a merge operation and check whether that query is being folded back to the source. You're looking for 'View Native Query' to be selectable and not greyed out:

BA_Pete_0-1643359506917.png

 

Either way, you should really be limiting PQ merges and aiming for a STAR/SNOWFLAKE relational schema in your data model instead.

 

Pete

Thanks Pete, this is helpful.

Hello - In addition to the tips provided by BA_Pete - 

@justlogmein 

 

If you really want to get technical, you can actually buffer your table to merge completely into memory using Table.Buffer(nameOfQuery) and merge this buffered table instead. This prevents the row-by-row calls that @jennratten describes, but I think we're probably getting a little beyond the scope of the original question with this. 🙂

 

Pete

That's interesting. A lot of the times when I merge it's because I just grouped by something and lost all my other columns, so I merge to restore them. I'm sure there is a quicker way for this.

Yes, you can definitely eliminate the need for merges in that scenario and just keep the columns in the group transformation.  You can do that by adding another aggregation column to the group operation that retains all of the columns.  Then after the table is grouped you can expand the columns.

 

jennratten_0-1643369501651.png

This is what it looks like after the grouping.  Then you can click to the side of one of the table objects and see all of the columns for that grouping.  You can click the expand button to choose which columns to expand, or select all column to expand with a list function.

Table.ExpandTableColumn(#"Grouped Rows", "Data", List.Difference(Table.ColumnNames(Table.Combine(#"Grouped Rows"[Data])), Table.ColumnNames(#"Grouped Rows")))

 

jennratten_2-1643369763991.png

 

Thanks for this. I tried this All Rows option in the past but it didn't give me the results I needed. I'll try again now though.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!