Log In
Register
Home
Active Member
May 4, 2020
Offline
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
November 8, 2013
Offline
Can you upload a sample file with the structure you described?
Active Member
May 4, 2020
Offline
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
November 8, 2013
Offline
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)
Active Member
May 4, 2020
Offline
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
November 8, 2013
Offline
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.
November 8, 2013
Offline
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...
1 Guest(s)
