Very slow transformation

[CloudMonkey]

Hi,

I have a transformation that is very fast in query editor (when I click on each of the steps in turn), but extremely slow (runs for more than an hour) when I "close and apply". Please do you know of any general reasons why this may happen? (I'm just connecting to a excel worksheet, transposing and filtering)

Thanks,

CM

[v-danhe-msft]

Hi @CloudMonkey ,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered to close this topic?

Regards,

Daniel He

[v-danhe-msft · Accepted solution]

Hi @CloudMonkey ,

The performance for your data model is related on many aspects, you could refer to below link:

https://www.thebiccountant.com/2017/05/29/performance-tip-partition-tables-crossjoins-possible-power...

Below link explains how you can use a process monitor to find out how much data Power Query reads from a file:

https://blog.crossjoin.co.uk/2018/07/19/using-process-monitor-to-find-out-how-much-data-power-query-...

Below link has some information about how Power Query caches data, which is particularly important if you have queries referencing other queries:

https://blog.crossjoin.co.uk/2016/11/20/referenced-queries-and-caching-in-power-bi-and-power-query/

Also if you don't need to preview all of the queries you can turn off Background Data; go to Query Options > Data Load > deselect 'Allow data preview to download in the background'.

Hope it could help you.

Regards,

Daniel He