Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors