Power Query slow refresh

Power Query
1

I am working with the Query Editor in Power BI desktop.

My issue is that when I make a changes to the queries, the refresh takes 10 or more minutes. When I get out of Query Editor and it asks to apply the changes, the refresh takes such a long time.

I think the refresh in the service is much faster. The main fact table has just over 1 million rows. The data is sitting in Sharepoint. It does go through a number of merges, filters, and group by’s.

In production, the refresh time doesn’t really matter. It will operate on a scheduled refresh every night at 2am.

However, when I am trying to do development work to tweak or alter the queries, everything is incredibly slow.

Has anyone else run into this issue? Any tricks so I can speed up work on my model in Power Query (Query Editor)?

2

Hi @BillK,

Here are some things you can consider/examine:

And
Performance tuning in Power Query and dataflows with Christopher Webb

I hope this is helpful

1 Like
3

Hi @BillK ,
The main problem will be that your data is on a SharePoint. That is no problem for the refresh in the service, but any changes to the query will take ages.

Matt Allington has several suggestion, of which “Develop on local file storage but deploy with SharePoint storage” is probably the best. Keep both source accessing steps in the code. Activate and deactivate with // in front. That is a very simple solution, easier to apply than the parameters he mentions.
Use Sharepoint.Contents() instead of Sharepoint.Files() does make a measureable difference while refreshing, but doesn’t really help for the development pain.

Fast Track Power BI Dev With SharePoint Files - Excelerator BI

5

Melissa,

Thanks for pointing out these 2 videos. This is a great in helping to improve speed and preformance.

Bill

6

Matthias,
Thanks for pointing Matt Allington’s article. I didn’t realize having the data in Sharepoint is what makes it so long to resfresh when doing development work. Sounds like a great trick to simply activate and deactivate with // in front. I’ll give it a try. The other day I added some comments and documentation to my M code and I was dying when I saw how long it took to refresh after such an insignificant change.

Bill

Bill

7

Bill, almost all my data is on SharePoint, so I know how it feels. :blush:

1 Like
8

Hi @BillK did the response provided by @Melissa and @Matthias help in solving your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

9

Matthias solved the problem.

However, I also read that dataflows can help. I was going to move to dataflows eventually. I set them up and it worked much better.