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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
GauravGG
Frequent Visitor

Why is Merging so Slow in Power Query?

I have a table named "Employee Snapshot"
It has dimensional attributes like Employee Class, Employee Code, Employee Status etc.

So, I am removing all the dimensional columns and keeping only dimension keys linked between Employee Snapshot & Dimension Tables

Steps done already
- Only have required columns in Snapshot table
- Have filtered only top 10 records
- Disabled multiple settings in Background
- Table.Join instead of Table.NestedJoin isn't working- may be I am unsure how to apply it

Now, my fact tables needs 14 Merge Operations to get 14 Keys instead of 14 Dimensional Values
The problem is that it keeps loading forever whenever I am making any development and is affecting my work massively

Please help- thanks

1 ACCEPTED SOLUTION
GauravGG
Frequent Visitor

I received help on Linkedin from Power Query Champion @rick De Groot
Wish I could tag him here

However, his response made me think deep and I redesigned the whole thing more efficiently
This is what he replied

GauravGG_0-1702470879765.png

 



View solution in original post

5 REPLIES 5
GauravGG
Frequent Visitor

I received help on Linkedin from Power Query Champion @rick De Groot
Wish I could tag him here

However, his response made me think deep and I redesigned the whole thing more efficiently
This is what he replied

GauravGG_0-1702470879765.png

 



bhanu_gautam
Continued Contributor
Continued Contributor

You can filter any one value in both the tables which you are merging than go back to the base table and remove filtered row from Steps taken it is one way to improve merging performance,

 

 

I have tried that- wasn't of much help

@GauravGG  there is no way to deny that PQ is slow and not possibly scalable for large dataset. However, make sure you stick to the following settings that might slightly improve the performance (however, I doubt if it would be any good for 14 joins).

 

Having said that, if it is a data-level task and you have premium capacity, you can bring all those tables to a datamart and use SQL endpoint to write fully qualified sql tables (basically data transformations shift from PQ to SQL). If you have Fabric, options are even wider (Blazing fast Scala, Apache Spark, Spark SQL, TSQL, Python, R).

 

If this is an anlysis-level task you can achieve this by building a data-model (it is the join that gets committed to the memory without needing explicit join) and writing DAX calculations.

 

 

smpa01_0-1701975817719.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

I tried the option you showed to disable Parallel Loading- didn't help much

Also, I am unsure if I understood this part of your response
If this is an anlysis-level task you can achieve this by building a data-model (it is the join that gets committed to the memory without needing explicit join) and writing DAX calculations

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors