Optimize performance in Power Query table & transformations

[Anonymous]

Hi guys, 

i am facing terrible performance in a power query table after a couple of joins and transformation. Let me give you a broad overview over the steps i do. It would be great to find out which steps most likely cause the slow performance and how to improve them. 

So i have a base table with 200 rows, 61 columns coming in to power query from an excel table. 

1) I unpivot the base table so that 13 columns will be unpivoted into two columns. In the 13 columns basically revenues for different countries are shown. The result is just two colums a) Country b) Revenue. Of course this make the table much longer than the 200 rows but also gives back less columns.

2) After that i join a new table with 5 columns. Then i add 5 custom columns where i multiply each of the joined columns with the revenue column from step before. Then i unpivot the 5 custom columns again. The tables now gets longer once more. 

3) I do some filtering, duplicate some columns, join 2 columns into one and unpivot another 5 columns. 

4) I join another table in with 15 columns. These are ratios on the revenue. I unpivot all the 15 colums so that im left with 2 columns a) the name of the ratio b) the ratio. 

5) I then multiply the ratios with the revenue in an additional custom column

6) then i PIVOT some colums, multiply them and then unpivot again. 

This is it. I'm not too familiar with Power Query transformations. But is it too much to unpivot and pivot all the time? What would be good ways to work around this? Or am carrying too many columns with me (~50)?

I mean i do quite some transformation but in the end the size of the table is < 200 K rows. How will you be able to something similar when tables get really big? I guess im doing sth fundamentally wrong here 😄

Would also be great if someone had a resource tip (book, youtube or antyhting) on how to optimize performance in Power Query.

Thanks alot already!

[manikumar34]

@Anonymous ,

I suggest not to do merging on Power query instead you can use Relationships because merging will efferct the performance. 

Unpivot will increase the number of records. let's say if you are doing on 15 columns the data set size will be increased by 15 times. 

After unpivoting try to Group the data to decrease the records.

Regards, 

Manikumar

[manikumar34]

@Anonymous,

You cannot create relationships in power query instead you can merge the tables. Without seeing your model and transformations you have applied,It is difficult to say where the performance is affected. If possible share a sample pbix file with same kind of data, connections and transformations. You can have a dummy data if the data is confidential.

Regards, 

Manikumar 

[Anonymous]

@manikumar34  but can i create relationships directly in power query? i only know them from power bi front end in the relationship view

[PhilipTreacy]

Hi @Anonymous 

No you can't create relationships in PQ.

Without seeing your data I can only offer general advice.  If you are only working with Excel tables then please just attach/link to the files.  Use OneDrive to store them and link to/share.

Phil

[PhilipTreacy]

Hi @Anonymous 

Hard to say what the issue is without seeing your data and the query, can you supply them?  

You could comment out every step, leaving just the first, then uncomment one line at a time and see where performance drops. 

Phil