• 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

Really Slow PQ Refresh|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Really Slow PQ Refresh|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 QueryReally Slow PQ Refresh
sp_PrintTopic sp_TopicIcon
Really Slow PQ Refresh
Avatar
Aravind Krishnakumar

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
May 31, 2020
sp_UserOfflineSmall Offline
1
May 31, 2020 - 10:22 pm
sp_Permalink sp_Print

Could someone help me my PQ refresh times.

i tried the background data Turn OFF method, with a little improvement.

May be the way i make the query is not efficient.

 

I have 30-40 workbooks each with 30-40 sheets.

am just trying to combine everything and put into a pivot table.

The query works, just that it takes 5-7 mins even after the background is off.

 

am attaching these

1. query file

2. Test File (which needs to be cleaned first)

 

Hope someone could help me out.

 

thanks in advance

Avi

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
June 2, 2020 - 2:06 pm
sp_Permalink sp_Print

Hi Aravind,

I was able to do a full refresh in under 1 minute, but I guess it's just the difference in computer performances.

You are using power pivots, the best option is to add Actual and Spare tables in the data model and relate them to Shots, instead of merging those 3 tables in power query, PP is much faster.

Also, the date table should not have missing days.

Best is to build a date table like this:

Min = Number.From(List.Min(CombinedData[invoice date])),
Max = Number.From(List.Max(CombinedData[invoice date])),
Source = Table.FromList({Min..Max}, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

Replace the red date column with yours.

Avatar
Aravind Krishnakumar

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
May 31, 2020
sp_UserOfflineSmall Offline
3
June 3, 2020 - 12:47 am
sp_Permalink sp_Print

Thank you catalin,

The table about the date, is to be created using a blank query right? And just paste it?

Sorry for not knowing what to do..

 

BTW, how was that less than a minute.. it takes over 3 mins in my computer and mine is fast.. with a lot of RAM and CPU power..

You saw that my sheets structure is way off, these are created by different departments and they send to me.. so I wanted to automate my work.

What I tried today was not use the promote headers, instead I put in the headers myself. That reduced another 30sec.

Power pivot is faster but am just confused on why it is not fast in my computer.

Just a thought.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
June 3, 2020 - 4:29 am
sp_Permalink sp_Print

Aravind Krishnakumar said

The table about the date, is to be created using a blank query right? And just paste it?
Sorry for not knowing what to do..
Power pivot is faster but am just confused on why it is not fast in my computer.

Yes, a blank query.

Go to advanced editor, and use this code:

let

Min = Number.From(List.Min(CombinedData[invoice date])),
Max = Number.From(List.Max(CombinedData[invoice date])),
Source = Table.FromList({Min..Max}, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in

Source

Make sure you replace the red text with your query references.

This is just the start, you can continue with adding year, month, quarter columns and any other date detail needed, using the PQ interface.

There are many things that can affect performance, not just RAM and CPU. For example, 32-bit or 64bit office can make a difference. I have a 64 bit office installation, but in the same time, the operating system and all programs, including office, are running on SSD, not HDD, this adds a significant performance improvement, at least on my computer.

Once you remove the table merging in power query and use power pivot relationships, you should see a significant improvement.

Avatar
Aravind Krishnakumar

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
May 31, 2020
sp_UserOfflineSmall Offline
5
June 8, 2020 - 10:20 pm
sp_Permalink sp_Print

Dear Catalin,

 

Thank you very much for your input.

i removed the merger table and used the relationships.

i also created custom function to read available sheets and which sheets I want.

also another function to process each sheet.

 

But I am stuck,

if I want to change something on a sheet, I just have to change it in the single sheet - but the function I created doesn't pick up the changes.

so I need to update the function everytime I make some changes.

is there a way to dynamically change the codes inside the function?

Hope you don't mind taking a look at the file.

 

The Date query  - I understood how to do, but the attached file I haven't updated.

thank you again for your input.

These does help me a lot.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
6
June 9, 2020 - 8:12 pm
sp_Permalink sp_Print

The changes you make in a sheet are not reflected instantly in power query and in power pivot.

You need to update the queries: Datatab-RefreshAll

Avatar
Aravind Krishnakumar

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
May 31, 2020
sp_UserOfflineSmall Offline
7
June 11, 2020 - 1:55 am
sp_Permalink sp_Print

I was wondering about functions I create.

Please correct me if am wrong.

What I did was,

made a function using a query with parameters, this created a folder group. When I opened the function and clicked advanced editor, I saw that the code inside the function is same as the query used to make the function.

Then I I had to modify the query, but the function Didn't change.

So Everytime if I had to make some changes, I have to manually make the changes to fuction as well as the query?

Or does the function take takes up all the changes to the query after the refresh!

 

I checked the refresh, and after invoking the function, the changes which I had made to the query(function) didn't happen! Which means the function was still old and the changes didn't happen inside it.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
8
June 11, 2020 - 4:01 am
sp_Permalink sp_Print

I have no idea what changes you have to do in the function. Usually, you have to avoid using hard typed file paths, or column names. Use generic functions, for example you can use Table.ColumnNames instead of hard typed lists.

Without examples to see what you have to do, I can only provide generic answers as well.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online:
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 205
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
stuart burge
Bruce Tang Nian
Scot C
Othman AL MUTAIRI
Misael Gutierrez Sr.
Attif Ihsan
Kieran Fee
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Forum Stats:
Groups: 3
Forums: 24
Topics: 6223
Posts: 27295

 

Member Stats:
Guest Posters: 49
Members: 31920
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.