• 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
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Problems with multiple level unpivotting|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Problems with multiple level unpivotting|Power Query|Excel Forum|My Online Training Hub

vba course banner

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 QueryProblems with multiple level unpivo…
sp_PrintTopic sp_TopicIcon
Problems with multiple level unpivotting
Avatar
Seenu Kandru

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
May 4, 2020
sp_UserOfflineSmall Offline
1
May 4, 2020 - 12:26 pm
sp_Permalink sp_Print

Hello,

This question is not about how to unpivot the data in Power Query.

I have an unpivotting process with three levels of headings. I know the process of unpivotting but I don't find a way to automate it since I get it everyday. My columns are as follows.

First merged column - Product Category: three column headers
Second merged column - Years: three years for each Product Category
Third merged column - Months: 12 months for each Year
Two Rows - Countries and Regions, Sales in the intersection.

I unpivot it using some transposes, fill-downs, merges and unmerges. I get this data daily. But when I have another excel sheet with the same raw data, I don't find a way to follow the same transformation process for my new data. I do not know which data source I need to take in the Power Query's new query. If I save all the raw data in different worksheets in a workbook, PQ is not taking all the sheets in to transformation. If I save as table/range transformation happens only for that table/range.

So I am confused on what to do, this question is not about the process I think, it is about the data source selection in the new query tab of PQ so that I can do it automatically.

Advance thanks

Srinivas

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
May 4, 2020 - 6:23 pm
sp_Permalink sp_Print

Can you upload a sample file with the structure you described?

Avatar
Seenu Kandru

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

Catalin,

I get this data in the form of Excel workbook from folder in my organization periodically.

Here is the link:

https://drive.google.com/file/.....sp=sharing

 

thanks for you effort.

Srinivas

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
May 5, 2020 - 2:28 am
sp_Permalink sp_Print

See an example attached.

I have separated the headers from data, joined them back, then appended the data.

(note: you can attach a file to forum message, links to external files will become broken in short time)

Avatar
Seenu Kandru

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
May 4, 2020
sp_UserOfflineSmall Offline
5
May 6, 2020 - 12:24 am
sp_Permalink sp_Print

Catalin,

Here is the link to the folder that has the target files. These are what I will receive everyday and I need to unpivot them every time. Sometimes there will be 10 files like these. But all are uniform in layout, not a single alphabet miss the queue. We can easily unpivot a single file, but for multiple files, the normal unpivotting process won't work out.

So please download the two files and do the un-pivoting process taking the folder as the source. For me it did not workout. Please check.

Thanks

https://drive.google.com/drive.....sp=sharing

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
6
May 6, 2020 - 3:56 am
sp_Permalink sp_Print

that folder has one file only, not 2, please check again.

Please upload the files here in forum, no need to add links here.

There is an attachment button below the message box.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
7
May 6, 2020 - 4:18 pm
sp_Permalink sp_Print

Anyway, I duplicated one of the files, check the attached file.

The process goes like this:

The main query gets the files from the folder. We add a new column with a formula that passes the content of the file to a function that transforms it to the format we need.

How to build this structure:

1. Create 4 separate queries: (Use simple names, no spaces)

- a query from folder (this will be the main query, will call the function: query from file)

- a query from file, that will list all sheets from that file (this will be converted to a function that calls the getsheetdata function and the getheaders function).

- a query that will process 1 sheet only to extract data (this will be converted to a function)

- a query that will process 1 sheet only to extract headers (this will be converted to a function)

2. Convert last 3 queries to functions

To convert a query to a function, you have to add a single line of code ABOVE the first "let" statement from the query:

(TheVariable)=> //use instead of TheVariable a relevant name for your operation, then replace inside query the hard typed text with this variable argument:

//your existing query:

let

Source=File.Contents(TheVariable) //this usually looks like: File.Contents("c:/Folder/test.xlsx") 

Depending on the data type you will pass to a function, you will have to remove first row (or rows) from the original query:

- if you pass a string, you don't have to remove rows, just replace the hard typed string with your function parameter.

- if you pass a table, locate the row in the query where the step requires a table as input, remove previous steps, then replace the reference with the variable parameter

if you pass binary content, locate the row in the query where the step requires binary data as input, remove previous steps, then replace the reference with the variable parameter

Example of binary data: File.Contents("c:/Folder/test.xlsx") (FileContents formula result is in binary format), so you have to replace the function name including path, not just the path.

 

Again, please upload the files here in forum. Do not provide links. Links will die...

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Andrew Wilkie, Malcolm Sawyer
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 216
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Melanie Ford
Isaac Felbah
Adele Glover
Hitesh Asrani
Rohan Abraham
Anthony van Riessen
Erlinda Eloriaga
Abisola Ogundele
MARTYN STERRY
Rahim Lakhani
Forum Stats:
Groups: 3
Forums: 24
Topics: 6355
Posts: 27792

 

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