Log In
Register
Home
April 25, 2017
Offline
I've had a chance to start to build out a costing model with a lot of data manipulations (e.g. category rollups and splits). To start small, I'm using some test data. The raw, test data has about 4 Excel tables, with anywhere from 3 to 30 rows of data in it. I also added 2 other Excel tables, each with 3,000 rows of data in them (about 3 columns wide).
In my model so far, I've got about 30 queries. It's not that I've built the queries any more granular than necessary, but it's the beginning of a very complex financial model, query-wise, although not math-wise. The model is solely in Power Query. Somewhere along the way in my model building, I've observed very slow Excel performance on the refresh. In particular, this occurs when I refresh any 1 of the 5 pivot tables I've built from the model, and also when I relaunch Power Query and click on any of the queries to view its data. The refreshes sometimes take about 30 secs to 1 minute, although not all the time.
My hypothesis is that it's not the data sources that are slowing things down, but the 30 or so queries I've built. I don't have any proof though. Since I'm building the model, I'm tinkering with it, and constantly refreshing it. So, although 1 minute is not a long time to wait, it feels like it especially when I'm making a small change to the model and waiting to update a pivot table to ensure I've made that change correctly.
I've looked online and one site said that PQ updates ALL the queries when you do a refresh, so the more queries, the longer it takes. Another site said to enable the Fast Data Load under Query Options. I've done that, but it doesn't seem to help much.
Is there any workaround to this issue? My source data is in the spreadsheet where I have my PQ model. I could move my source (Excel) data back out to external XLS files, but I would think that this would only slow things down further. I do have 8 GB RAM. Any thoughts would be appreciated.
Thanks,
Cory
See Answer
July 16, 2010
Offline
Hi Cory,
Power Query can push processing back to external databases like SQL etc., which is called query folding. However, as your data is in Excel it won't fold the queries anywhere. In other words, Excel is doing all the work as query folding to an external database isn't available.
You might want to try moving your source data to a CSV file to see if this improves performance, as described here.
This post explains how you can use a process monitor to find out how much data Power Query reads from a file.
This post has some information about how Power Query caches data, which is particularly important if you have queries referencing other queries.
Hope that helps.
Mynda
April 25, 2017
Offline
Hi Mynda,
To speed up performance of my small example, I tried converting all the Excel tables to external .CSV sources and then updating my queries to read them. the good news is that I improved performance when opening the file and query editor from scratch. The bad news, when I opened the last query in the stack, is that performance improved only from 4 min, 8 secs to 3 min, 50 secs. So, unfortunately, that didn't help that much.
I did appreciate the article in your 2nd link in learning more about how PQ caches data. In particular, that article led me to others which talk about the Table.Buffer command. That put me into the deep end of the pool though in that I'm trying to figure out where that should be use (on both source and subsequent queries) and exactly what the syntax is. So, that would be a great topic for a future blog entry on exactly how to use that.
Another site recommended doing as much manipulation in DAX (PP) as possible since the compute engine is faster in DAX than it is in M. The caveat to that advice was to keep the manipulations in PP in case you need it for subsequent queries, which I do.
I'll continue to investigate how to use Table.Buffer in my queries to see if and how this can improve performance though.
Regards,
Cory
July 16, 2010
Offline
Hi Cory,
Another idea is if you don't need to preview all of the queries you can turn off Background Data; go to Query Options > Current Workbook: Data Load > deselect 'Allow data preview to download in the background'.
The syntax for the buffer functions is to simply wrap it around the Source line of code e.g.
Binary.Buffer (preferred):
Source = Excel.Workbook(Binary.Buffer(File.Contents("C:\mynda\my_file.xlsx")), null, true),
Table.Buffer:
Source = Table.Buffer(Excel.Workbook(File.Contents("C:\mynda\my_file.xlsx"), null, true)),
Mynda
Answers Post
April 25, 2017
Offline
Hi Mynda,
Thanks for your suggestion about turning off Background Data in Power Query. That was a game changer! That's the only change I made and it allowed my queries to be refreshed in a matter of seconds. I haven't tried using the Excel buffer command, but I may need to try it when I load my model with larger amounts of data. But at least, I now have the correct syntax for using the buffer command, if needed.
Thanks again.
Cory
July 16, 2010
Offline
Great! So glad it was a simple change.
New Member
April 4, 2019
Offline
Mynda Treacy said
Great! So glad it was a simple change.
I registered on here just to say thank you as thats saved so much time! Thanks!
July 16, 2010
Offline
🙂 glad I could help, Joe!
New Member
May 4, 2019
Offline
I have an Excel file where I was stacking queries pulling from Salesforce. I simply tried your tip Mynda and it changed my refresh time from 15-20 minutes to 3! This is awesome, Thanks!
New Member
June 16, 2019
Offline
Thanks Mynda! Turning off the data preview literally saved my life! Thanks! 😀
New Member
August 3, 2018
Offline
Mynda Treacy said
Hi Cory,Another idea is 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'.
The syntax for Table.Buffer is to simply wrap it around the Source line of code e.g.
Table.Buffer(Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]),
I would apply it to the first query, assuming that is the one that your other queries are referring to and see if that helps.
Mynda
Hi Mynda,
Let me add my kudos and praises for this elegant solution. My workbook takes very long to refresh 23 queries. I've been wrestling with it all day! This solution radically speeds things up!
July 16, 2010
Offline
Great to know it was helpful, Gary!
Active Member
November 6, 2019
Offline
Hey Mynda
My problem is also regarding slow refresh but my database is google sheets.
I have few google sheets with around 150-200 tabs in each sheet, though the data in those sheets is not more than 20 columns and 100 rows. And the file size is just 3 MB.
Still the refresh time is more than 20 minutes. Also any change I do in like changing data type or deleting any column takes 2-3 minutes. And if I have to save the file its almost like a nightmare. I can take a nap, have lunch and do some shopping till the time it get saved. I hope now you can imagine how slow it is.
Any suggestions what should I do for a faster refresh?
Thanks in advance 🙂
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Mayank,
You can take a look at this topic: https://www.myonlinetraininghu.....-very-slow
As you can see there, it's a matter of what your query does. As I said before, trying to imitate excel functions in PQ might lead to highly inefficient queries.
You will have to provide more details to see what can be improved.
Active Member
November 6, 2019
Offline
Hi Catalin
Thank you for the response.
Here I am adding a sample file (though its not the complete file, but I hope it would be helpful in clearing the situation).
This sheet contains just 3 tabs where as the original one contains over 100 tabs.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
There is no query in the file you provided, how can I find a reason for slow query when there is no query?
As mentioned, I have to see what your query does, the steps you applied.
Active Member
November 6, 2019
Offline
Hi Catalin
I am sorry but I never said I have an issue regarding slow query. I guess I didnt made my self clear enough and I appologize for that.
Actual issue is I have my data stored in google sheets (and to know the kind of data I have you can always refer to sample sheets provided above), now when I get data in power BI via web link. From there onwards everything gets slow.
I have 10 workbooks with 150-200 sheets in each one of them. Though right now I have uploaded only one workbook and BI started to hang. Imagine what if I add all remaining 9 workbooks also.
I know PBI is capable of handeling huge data campared to which I my data is very small, even though PBI drastically slows down.
My guess is as PBI have no integration with Google sheets that could be a reason (I may be wrong, if I am wrong then please forgive me as I am just a beginner)
Thanks in advance
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
The only way to get data into PBI is through power Query. PBI has power query and power pivot as modelling tools. Unlike excel version, you cannot bring data directly in Power Pivot, so you have a query from a website. (google sheets in your case). If the refresh time is slow, then the query is slow.
The refresh time is strictly related to what your query does, and the measures you wrote. I hope you realize that the information you've provided is not enough to understand why the refresh takes a long time.
New Member
April 14, 2021
Offline
This is the beauty of the internet, almost 3 years later and this post is still solving problems, Thanks Mynda, you literally gave me days of report preparation with this tip.
The following users say thank you to John Nwakanma for this useful post:
Mynda Treacy
1 Guest(s)
