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

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
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors