High Loading Times in Power Query

[fernandoC]

Hi!

I've been experiencing high loading times in PQ on a couple of my tables. 

I've noticed that after merging these tables the loading times when doing any transformation to that table takes between 20-40 minutes. I understand that by not using merges the loading times would improve but I currently need them to obtain some specific visuals. 

I checked a couple of articles stating that disabling " Allow Preview Data Load" would help, I have unchecked it but the loading times are still the same. 

Any suggestions?. 

[V-lianl-msft]

Hi @fernandoC ,

What kind of visual do you want to create by merging tables? Maybe you could try using DAX instead of merging tables.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[fernandoC]

Hi @V-lianl-msft ,

There's a couple of merges going on in my fact table, however, I think the main one is the following. I have a table that contains the 'Custom Fields' within a Applicant Tracking System, each of these 'Custom Fields' have distinct values for each of the values within the fact table. I created the merge and then a pivot of the column as I needed to have a column for each one of the custom fields with their respective values. 

First I needed to have these as columns since I'm building specific KPI's for specific Custom Fields, and I wanted to reduce the amount of rows as the custom fields repeat over and over for each candidates created within the system. I understand that this is creating high loading times but I haven't found a more elegant way to resolve it. 

Hope it makes sense. 

Any further questions please let me know.

Best, 

[lbendlin]

Suggestion is to avoid merging in Power Query as much as possible.  Check for the performance of the different join options.

Choose the Right Merge Join Type in Power BI - RADACAD

Or do the merge in the Power BI data model instead (that will be done in memory, so will be much faster)