Excel Forum

Problems with multiple level unpivotting

Seenu Kandru

Active Member

Members

Forum Posts: 3

Member Since:
May 4, 2020

Offline

May 4, 2020 - 12:26 pm

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

Catalin Bombea

Iasi, Romania

Member

Dashboards

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

May 4, 2020 - 6:23 pm

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

Seenu Kandru

Active Member

Members

Forum Posts: 3

Member Since:
May 4, 2020

Offline

May 5, 2020 - 12:29 am

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

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

May 5, 2020 - 2:28 am

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)

Seenu Kandru

Active Member

Members

Forum Posts: 3

Member Since:
May 4, 2020

Offline

May 6, 2020 - 12:24 am

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

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

May 6, 2020 - 3:56 am

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.

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

May 6, 2020 - 4:18 pm

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...