Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Many queries in Power Query - low performance

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 🙂

3 ACCEPTED SOLUTIONS
mussaenda
Super User
Super User

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.

View solution in original post

Jimmy801
Community Champion
Community Champion

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

View solution in original post

v-yingjl
Community Support
Community Support

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.

 

 

 

View solution in original post

3 REPLIES 3
v-yingjl
Community Support
Community Support

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
Community Champion
Community Champion

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
Super User
Super User

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors