• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Any way to speed up really slow refresh times in Power Query?|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Any way to speed up really slow refresh times in Power Query?|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryAny way to speed up really slow ref…
sp_PrintTopic sp_TopicIcon
Any way to speed up really slow refresh times in Power Query?
Page: 12Jump to page
Avatar
Scotty81
Member
Members
Level 0
Forum Posts: 33
Member Since:
April 25, 2017
sp_UserOfflineSmall Offline
1
September 15, 2018 - 7:10 am
sp_Permalink sp_Print

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

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4446
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
September 15, 2018 - 7:51 am
sp_Permalink sp_Print

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

Avatar
Scotty81
Member
Members
Level 0
Forum Posts: 33
Member Since:
April 25, 2017
sp_UserOfflineSmall Offline
3
September 21, 2018 - 2:41 am
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4446
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
September 25, 2018 - 7:55 pm
sp_Permalink sp_Print sp_EditHistory

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

sp_AnswersTopicAnswer
Answers Post
Avatar
Scotty81
Member
Members
Level 0
Forum Posts: 33
Member Since:
April 25, 2017
sp_UserOfflineSmall Offline
5
October 10, 2018 - 1:31 am
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4446
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
October 10, 2018 - 8:57 am
sp_Permalink sp_Print

Great! So glad it was a simple change.

Avatar
Joe Parkinson

New Member
Members
Level 0
Forum Posts: 1
Member Since:
April 4, 2019
sp_UserOfflineSmall Offline
7
April 4, 2019 - 12:58 am
sp_Permalink sp_Print

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!

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4446
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
8
April 4, 2019 - 9:35 am
sp_Permalink sp_Print

🙂 glad I could help, Joe!

Avatar
Justin Post

New Member
Members
Level 0
Forum Posts: 1
Member Since:
May 4, 2019
sp_UserOfflineSmall Offline
9
May 4, 2019 - 1:43 am
sp_Permalink sp_Print

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!

Avatar
Joryi Tan

New Member
Members
Level 0
Forum Posts: 1
Member Since:
June 16, 2019
sp_UserOfflineSmall Offline
10
June 16, 2019 - 1:39 pm
sp_Permalink sp_Print

Thanks Mynda! Turning off the data preview literally saved my life! Thanks! 😀

Avatar
Gary Mark

New Member
Members
Level 0
Forum Posts: 1
Member Since:
August 3, 2018
sp_UserOfflineSmall Offline
11
August 6, 2019 - 11:27 am
sp_Permalink sp_Print

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!

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4446
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
12
August 6, 2019 - 4:40 pm
sp_Permalink sp_Print

Great to know it was helpful, Gary!

Avatar
mayank salvi

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
November 6, 2019
sp_UserOfflineSmall Offline
13
November 6, 2019 - 9:41 pm
sp_Permalink sp_Print sp_EditHistory

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 🙂

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
14
November 7, 2019 - 2:53 am
sp_Permalink sp_Print

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.

Avatar
mayank salvi

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
November 6, 2019
sp_UserOfflineSmall Offline
15
November 7, 2019 - 4:24 pm
sp_Permalink sp_Print

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.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
16
November 7, 2019 - 9:08 pm
sp_Permalink sp_Print

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.

Avatar
mayank salvi

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
November 6, 2019
sp_UserOfflineSmall Offline
17
November 9, 2019 - 12:40 am
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
18
November 9, 2019 - 2:50 am
sp_Permalink sp_Print

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.

Avatar
John Nwakanma

New Member
Members
Level 0
Forum Posts: 1
Member Since:
April 14, 2021
sp_UserOfflineSmall Offline
19
April 14, 2021 - 7:01 pm
sp_Permalink sp_Print

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
Avatar
Vidal Nagutom Jr.

New Member
Members
Level 0
Forum Posts: 1
Member Since:
April 30, 2021
sp_UserOfflineSmall Offline
20
April 30, 2021 - 10:11 am
sp_Permalink sp_Print

I agree with you John. Thanks Mynda!

Page: 12Jump to page
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Roy Lutke, Richard West, Jeff Krueger, Tom VAN LOO, Kylara Papenfuss, Nada Perovic
Guest(s) 7
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Naomi Rumble
Uwe von Gostomski
Jonathan Jones
drsven
Forum Stats:
Groups: 3
Forums: 24
Topics: 6212
Posts: 27236

 

Member Stats:
Guest Posters: 49
Members: 31889
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.