• 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

Working in and/or refreshing queries has become unbearably slow|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Working in and/or refreshing queries has become unbearably slow|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 QueryWorking in and/or refreshing querie…
sp_PrintTopic sp_TopicIcon
Working in and/or refreshing queries has become unbearably slow
Avatar
Jason Davis

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
November 10, 2020
sp_UserOfflineSmall Offline
1
November 10, 2020 - 5:28 am
sp_Permalink sp_Print sp_EditHistory

Hi folks,

I'm a Data Analyst who has stumbled his way into using Power Query as a solution for flattening our platform data into something more useful and tailor made in a relatively automated fashion. I've never bumped into any major issues until now. My workbook has become sluggish to the point of near unusability. Let me try to spell out some details:

I'm extracting some oddly structured data focused around project details along with the projects' financials (Budget, Forecast, Actuals) by month and category. The data is laid out something like this:

Project Number| Project Name | Detail 1 | Detail 2 | ... | Detail 19 | Report Created | Report Type | Month 1 Internal | Month 1 External | Month 1 Equipment | Month 2 Internal | Month 2 External | Month 2 Equipment | ... | Month 12 Internal | Month 12 External | Month 12 Equipment

Currently, this is 101 Columns x 2341 rows, or 236,441 cells.

Query "Prep" is used first to grab and manipulate the raw data. This query moves in the typical rapid pace. I use it to do a variety of transformations, including:

  • Unpivot the 84 month fields (this report uses the cost categories, but not the month spread)
  • Date Calcs based around the Report Created field
  • Determine whether certain month data is to be included or excluded (i.e. Actuals for future months won't be included, Forecast for past months won't be included)
  • Concatenate Report Type and Cost Category attributes into full labels such as "Budget: Internal" or "Actuals: Equipment" 
  • I end with a Pivot/Unpivot back to back to collapse separate month's data into single rows

This produces data at 17 Columns x 14218 rows, or 241,706 cells.

Next I have a fork where two separate queries ("Project Level" and "Program Level") are referencing "Prep" and performing mirrored calculations, but at differing levels of detail. These queries seem to be where the problems start. Making the smallest changes such as renaming a step causes the query window to seize and process for 2+ minutes. Here's a quick rundown of what these queries are doing:

  • Pivot the labels created in Prep
  • Calculate several "Total" columns for shared Report Types (i.e. Budget: Internal + Budget: External + Budget: Equipment = Budget Total)
  • Create a constant label for the level of Detail ("Project" for Project Level, "Program" for "Program Level")
  • Calculate a few differences and percentages such as "Percent of Budget remaining" or "Project Budget Variance"
  • Calculate a label based on the size of percentage variance (i.e. if [Project Budget Variance %] > 0.1 then "Over Budget")
    • If I had to speculate where my problem is coming from, this could be it. I was dealing with a null data issue, so this is how this calc looks:
      • = Table.AddColumn(#"Changed Type2", "Custom", each if [Percent Of.1] = null then "Incalculable"
        else if Number.Abs([Percent Of.1]) < .1 then "On Budget"
        else if [Percent Of.1] > .1 then "Over Budget"
        else if [Percent Of.1] < -.1 then "Under Budget"
        else "Other")

Finally I take those two queries and Append them to one another to create an illusion that each set of Projects has a subtotal row underneath it at the Program level.

 

So, with all of that being said, does anyone have ideas of where I may be having issues? If posting the M code from one or more of my queries would help, I'd be happy to do that. Thanks in advance for any and all help! 🙂 

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1510
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
2
November 10, 2020 - 3:43 pm
sp_Permalink sp_Print

Hi Jason,

Can you post the file(s) containing the queries, and any necessary source data, so we can have a look.

Thanks

Phil

Avatar
Jason Davis

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
November 10, 2020
sp_UserOfflineSmall Offline
3
November 11, 2020 - 12:59 am
sp_Permalink sp_Print

Hi Phil,

 

Thanks for the response and the suggestion. The data itself is sensitive, so I'm not sure I can post the file as is. I'll see what I can come up with that would still provide adequate detail and hopefully post that for feedback.

 

Regards

 

Jason

Avatar
Jason Davis

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
November 10, 2020
sp_UserOfflineSmall Offline
4
November 11, 2020 - 6:16 am
sp_Permalink sp_Print

As Phil requested, I'm uploading a file demonstrating the data and queries. I took a subset of the data, and tried to mask any important details with silly labels. The data subset as pulled from our system is on sheet 1, the first prep results on sheet 2, the forked levels of detail on 3a and 3b, and the final blended result on sheet 4. 

Naturally, now that there is substantially less data (13 projects of the original 440), the queries seem to be speeding through without a hitch.

In any case, I'm still having troubles with the full data set, so any advice people have for optimizing or improving my queries would be greatly appreciated!

Avatar
Jason Davis

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
November 10, 2020
sp_UserOfflineSmall Offline
5
November 13, 2020 - 2:51 am
sp_Permalink sp_Print

So I realize there hasn't been real discussion of this to speak of, but I seem to have resolved the issue myself and thought I should share. I began to realize I had a lot of attribute columns that were static throughout 95% of the transformative process, and guessed that PowerQuery may be spending resources reading and reading these unnecessarily. So I stripped my source data sheet down to the bare essentials. 

I then used a SQL query to pull the Project Detail data I had removed and joined it into my Project Level and Program Level queries as late as possible.

This shift has reduced my cumulative query time by roughly 90% (~10 minutes down to ~1 minute). 

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4438
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
November 13, 2020 - 9:39 am
sp_Permalink sp_Print

Glad you found some efficiency gains, Jason and thanks for reporting back. However, something concerns me with what you describe. You say:

 

"I stripped my source data sheet down to the bare essentials. I then used a SQL query to pull the Project Detail data I had removed and joined it into my Project Level and Program Level queries as late as possible." 

 

Where is the data sheet? The term data sheet implies it's in an Excel, CSV or Text file, but you say you're getting it with a SQL query, so I'm puzzled.

If your data is in a SQL database then you should use Power Query to get the data and at the first step, remove the columns you don't need. This will have the same effect as removing them from the source data.

Also, you should avoid writing your own SQL query and pasting that in the Advanced dialog box in Power Query's Get SQL Data window. The reason for this is that when you write your own SQL query, Power Query can no longer fold the processing back to the SQL database. Instead, Power Query must do all the work, and often it's way less efficient than a powerful SQL database.

I hope that all makes sense and helps you further optimise your query.

Mynda

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: David, yonatan zelig, Christopher Anderson
Guest(s) 11
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:
yashal minahil
Oluwadamilola Ogun
Yannik H
dectator mang
Francis Drouillard
Orlando Inocente
Jovitha Clemence
Maloxat Axmatovna
Ricardo Freitas
Marko Meglic
Forum Stats:
Groups: 3
Forums: 24
Topics: 6200
Posts: 27182

 

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