Forum Discussion

GauravGG's avatar
GauravGG
Frequent Visitor
2 years ago
Solved

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 dime...
Need Help
  • GauravGG's avatar
    GauravGG
    2 years ago

    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's avatar
GauravGG
Frequent Visitor
2 years ago

I have tried that- wasn't of much help

smpa01's avatar
smpa01
Community Champion
2 years ago

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.

 

 

 

  • GauravGG's avatar
    GauravGG
    Frequent Visitor
    2 years ago

    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