Many queries in Power Query - low performance

[Anonymous]

Hello,

I've been struggling with such an issue.

I have a file which has more than 100 queries in Power Query and refreshing is extremely slow. I definitely need to improve performance as this can take up to 4 hours to refresh all the datasets. However, when I try to do some diagnostics or check the code in order to improve it, it loads very, very slow and I'm sometimes not even able to change anything.

Do you have any ideas how I can tackle that? I thought maybe it is possible, for example, to extract the raw underlying M codes from all queries and try to split/rewrite/optimize them outside Power Query?

Thank you in advance for your help 🙂

[mussaenda · Accepted solution]

Hi @Anonymous,

In my opinion,

The first thing you can do to handle the queries is to limit the data it is query-ing.

After that, you are able to check all the steps then clean or improve.

Instead of 10000rows,

try with 500 rows only.

Then check the queries.

It might not make your queries faster but it might help.

Hope this helps.

[Jimmy801 · Accepted solution]

Hello @Anonymous 

you can extract all PQ code by selecting all queries and then right-click -> copy and pasting into an editor. But as M-language is nowhere supported I ask myself how you can possibly rewrite all your code without having intellisense and objects. Also to bring them back into power query could be tedious as well.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[v-yingjl · Accepted solution]

Hi @Anonymous ,

You can try to use Table.buffer() to buffers a table in memory.

Here are some articles that you can refer:

1. This post explains how you can use a process monitor to find out how much data Power Query reads from a file.
2. This post has some information about how Power Query caches data.

In addition, there are some performance optimization tips in this viedo and article:

1. Power Query Performance Optimization 
2. Power BI Performance Optimization Tips 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jimmy801 · Accepted solution]

Hello @Anonymous 

you can extract all PQ code by selecting all queries and then right-click -> copy and pasting into an editor. But as M-language is nowhere supported I ask myself how you can possibly rewrite all your code without having intellisense and objects. Also to bring them back into power query could be tedious as well.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[mussaenda · Accepted solution]

Hi @Anonymous,

In my opinion,

The first thing you can do to handle the queries is to limit the data it is query-ing.

After that, you are able to check all the steps then clean or improve.

Instead of 10000rows,

try with 500 rows only.

Then check the queries.

It might not make your queries faster but it might help.

Hope this helps.