• 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

Merging to unpivoted tables and using that in pivot table|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Merging to unpivoted tables and using that in pivot table|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
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 QueryMerging to unpivoted tables and usi…
sp_PrintTopic sp_TopicIcon
Merging to unpivoted tables and using that in pivot table
Avatar
Leonardo Poeti

New Member
Members
Level 0
Forum Posts: 1
Member Since:
March 30, 2021
sp_UserOfflineSmall Offline
1
April 1, 2021 - 3:47 am
sp_Permalink sp_Print

Hi all,

I am quite new to pivot queries and have marginally achieved what I want except as the data has grown the time to refresh has increased considerably and in some cases I get the message that there is insufficient memory.

First off let me state that I am currently forced to use Excel 2013 for this work.

The spreadsheet I am am working on has 2 sheets each with a matrix of engineering data of sized 155 rows by 124 and 92 columns respectively. For example matrix one maps Functions vs Tests and Matrix 2 maps the same Functions vs Hardware (the mapping variables are not necessarily the same and the source information comes from different areas)

I have used 2 power queries one to unpivot each matrix. And have set these as connection only as there is no need to visualise the unpivoted tables.

I then use a third query to do a merge where I do LeftOuter Join and Expand to keep only the The Hardware type and Hardware Mapping. This is also set as connection only.

This 3rd Query is then used as the source for a pivot chart in a new sheet which enables slicers for Functions and Hardware and Tests to be implemented so users can slice by hardware and find applicable tests or vice versa.

(I attached a sample which should give the idea of what I am trying to do. The small sample works fine...but merging the 15k plus row tables seems to cause issues and slow performance.)

Having read through some of these posts and referenced articles to Chris Webb's Blog, I think that there should be a way to optimise this spreadsheet but have not been able to do so. There are 2 points that I have picked up on but not sure if or how I should implement in my case:

1 - is the use of a Primary key or Table.AddKey() but in my case the fields I am merging on are no longer unique due to the unpivot and also I am doing an expand and not an aggregate. So should I still be having a unique key and if so how would I implement it?

2 - is the use of Table.Buffer(), which I am also not sure if I should apply or to which query? (in my case I am not expecting users to modify the data so I would not need the queries to be rerun after the first time. Though I have the feeling that the 3rd query gets rerun depending on what you do with the pivot chart...for example I tried grouping a field in the pivot and when I tried to ungroup excel did not cope and ran out of memory)

(Note also I have unchecked allow data preview and am not using fast data load as it says excel will remain unresponsive for long periods and it is already unresponsive following a refresh...not really understood how the Fast Data option should help)

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1518
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
2
April 2, 2021 - 12:10 pm
sp_Permalink sp_Print

Hi Leonardo,

You can add a key to a table using the Table.AddKey() like so

Keyed_test_map = Table.AddKey(test_map, {"Function"} , true)

Whether it will make a difference?  Not sure. Chris Webb seems to think it only makes an improvement if you are aggregating after the merge. No harm in trying it and seeing what happens.

As for Table.Buffer, you can buffer the tables inside the Merge query before doing the join, and change the table names in the Source step to match

Buffered_test_map = Table.Buffer(Keyed_test_map),
Buffered_hardware_map = Table.Buffer(hardware_map),
Source = Table.NestedJoin(Buffered_test_map, {"Function"}, Buffered_hardware_map, {"Function"}, "hardware_map", JoinKind.LeftOuter)

The 3rd query won't get rerun no matter what you do with the chart.

I've made all of these changes in the attached file.  Replicate them in your real file and see if they make a difference.  Let me know, I'm curious to find out.

Regards

Phil

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: andria young, RAMEZ ATTAR, Bruce Tang Nian
Guest(s) 10
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:
Bruce Tang Nian
Scot C
Othman AL MUTAIRI
Misael Gutierrez Sr.
Attif Ihsan
Kieran Fee
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Bhuwan Devkota
Forum Stats:
Groups: 3
Forums: 24
Topics: 6222
Posts: 27293

 

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