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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

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

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors