How to reuse a transformation process in unpivotting

R

R1C1

New member
Joined
May 4, 2020
Messages
9
Reaction score
0
Points
0
Excel Version(s)
Microsoft Excel 2016 Pro Plus
Hi Excel Guru,

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
 
Where is the data sourced from?
 
Hello Phillips,

The data source in my case is Excel workbooks from a folder which will be generated within my organization periodically.
 
Can you post your PQ code as well, and tell us what is going wrong, as I didn't quite get what you said before.

Is the problem that each day is a different file, and you don't know what it is called beforehand?​
 
Phillips,

I do not have any code. I am still figuring out on how to do this process automatically for multiple workbooks that I receive everyday. I can process one file at a time but not multiple workbooks which are in the same format.

FYI, the file type is same every day i.e. Excel workbook shared in particular folder.

If I understand your question properly, "Are asking me to process the single file once and send you the PQ code?"

thanks
Srinivas
 
Now you have really confused me. In post #1 you say that you have an unpivotting process,. noiw yuou say you don't have any PQ code. Exactly what help are you after? Do you want some unpivotting code with nested headers, or do you jusrt want to know how to get multiple files from a folder. If the latter, PQ proivides that facility, Get & Transform>New Query>From File>From Folder. It will ask you to browse to the folder, and then it will show you all the files. Double click the double down arrow to the right of the Content header and they will all open appended, then you can do the transposing, filling and unpivotting.
 
Phillips,

I want to "know the way to do the un-pivotting process for multiple files (which are from a folder) automatically". I know how to un-pivot a single file. When I have multiple files in a folder, and tried to import the same into PQ, at the preview pane I can see and import only the first file. I couldn't file the other files for some reason. That's where the problem. So I thought I can change the source from folder to Excel workbook, so that I can process that in a diff way. Please don't hesitate ask me if it is still not clear.

thanks
 
Phillips,

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

https://drive.google.com/drive/folders/1cJu_W-Xr1AeP1aCVzoYhWtsV5QBbW1fx?usp=sharing

Thanks
 
They don't have the same sheet name, the transform is based upon the same sheet name Sheet1.
 
OK, If they have same sheet name, can you do the unpivotting process in any way? I tried the same with same names also but only the first sheet has been imported into PQ. I could not see the second sheet in import process at all. There was no option to select multiple files either. That's where the whole problem. Can you do that successfully and let me know please if that work for you?

I came to the conclusion that we can't do this with PQ for this kind of files.

Srinivas
 
Did you do the File>Folder optio? That gets youi both (all) files.
 
I changed the sheet name, and then I unpivotted it fine. You need to remove the null rows created by the headings in the other filkes, biut other than that, no problems.
 
Hi all,

thanks for your initiative.

In one of the data transforming processes in my organization, We receive multiple Excel workbooks with a single Excel sheet in each workbook with merged columns (Pls go thru the thread for background of the problem). All the Excel sheets will be identical in the layout..just the numbers will change, need not to say every month sales figs change. We need to un-pivot them to create a meaningful report. I know how to un-pivot a single Excel worksheet but not many. For multiple Excel files, the obvious import option is From Folders. When I choose this option and proceed further, what I see / import is the first Excel file ONLY.

My question is How to Un-pivot multiple Excel files with merged columns ?

I have attached the two sample files, try to merge both and let me know the process, because we get these everyday, so I don't want to do it repeatedly. I need some help in the automation.

Thank you very much
Srinivas
 

Attachments

  • OldSales.xlsx
    194.3 KB · Views: 10
  • NewSales.xlsx
    194.8 KB · Views: 10
I ran it for your files, picked the Transform Data option, then chose Sheet1, and it opened both files fine. It created a group of transform files.

I then unpivotted it with the fiollowing code

Code: 
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]let
    Source = Folder.Files("C:\Users\Bob\Documents\Projects\_8 Community\Forums\ExcelGuru\XLGuru 10601 - How to Reuse a Transformation Process in Unpivotting"),
    #"Filtered Hidden Files2" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function2" = Table.AddColumn(#"Filtered Hidden Files2", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns2" = Table.RenameColumns(#"Invoke Custom Function2", {"Name", "Source.Name"}),
    #"Removed Other Columns2" = Table.SelectColumns(#"Renamed Columns2", {"Source.Name", "Transform File"}),
    #"Expanded Table Column2" = Table.ExpandTableColumn(#"Removed Other Columns2", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table Column2",{"Source.Name"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Inserted Merged Column" = Table.AddColumn(#"Filled Down", "Merged", each Text.Combine({Text.From([Column1], "en-GB"), [Column2]}, "|"), type text),
    #"Reordered Columns" = Table.ReorderColumns(#"Inserted Merged Column",{"Merged", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87", "Column88", "Column89", "Column90", "Column91", "Column92", "Column93", "Column94", "Column95", "Column96", "Column97", "Column98", "Column99", "Column100", "Column101", "Column102", "Column103", "Column104", "Column105", "Column106", "Column107", "Column108", "Column109", "Column110", "Column111", "Column112", "Column113", "Column114", "Column115", "Column116", "Column117", "Column118", "Column119", "Column120", "Column121", "Column122", "Column123", "Column124", "Column125", "Column126", "Column127", "Column128", "Column129", "Column130", "Column131", "Column132", "Column133", "Column134", "Column135", "Column136", "Column137", "Column138", "Column139", "Column140", "Column141", "Column142", "Column143", "Column144", "Column145", "Column146", "Column147", "Column148", "Column149", "Column150", "Column151", "Column152", "Column153", "Column154", "Column155", "Column156", "Column157", "Column158", "Column159", "Column160", "Column161", "Column162", "Column163", "Column164", "Column165", "Column166", "Column167", "Column168", "Column169", "Column170", "Column171", "Column172", "Column173", "Column174", "Column175", "Column176", "Column177", "Column178", "Column179", "Column180", "Column181", "Column182", "Column183", "Column184", "Column185", "Column186", "Column187", "Column188", "Column189", "Column190", "Column191", "Column192", "Column193", "Column194", "Column195", "Column196", "Column197", "Column198", "Column199", "Column200", "Column201", "Column202", "Column203", "Column204", "Column205", "Column206", "Column207", "Column208", "Column209", "Column210", "Column211", "Column212", "Column213", "Column214", "Column215", "Column216", "Column217", "Column218", "Column219", "Column220", "Column221", "Column222", "Column223", "Column224", "Column225", "Column226", "Column227", "Column228", "Column229", "Column230", "Column231", "Column232", "Column233", "Column234", "Column235", "Column236", "Column237", "Column238", "Column239", "Column240", "Column241", "Column242"}),
    #"Transposed Table1" = Table.Transpose(#"Reordered Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Removed Top Rows" = Table.Skip(#"Promoted Headers",2),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Top Rows", {"Salesman", "Country", "Region"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", Int64.Type}, {"Attribute.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Attribute.1", "Year"}, {"Attribute.2", "Month"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Salesman", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if [Salesman]=null then 0 else 1),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1))
in
    #"Filtered Rows"[/FONT]
 
Last edited:
In a hurry
Code: 
let    fnTransform = (b as binary, fileName as text) =>
        let
            Extract = Excel.Workbook(b),
            #"Filtered Rows1" = Table.SelectRows(Extract, each ([Kind] = "Sheet") and ([Hidden] <> true)),
            RemOtherCols = Table.SelectColumns(#"Filtered Rows1",{"Data"}){0}[Data],
            #"Kept First Rows" = Table.FirstN(RemOtherCols,3),
            #"Transposed Table" = Table.Transpose(#"Kept First Rows"),
            ChTypes = Table.TransformColumnTypes(#"Transposed Table", {{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
            #"Filled Down" = Table.FillDown(ChTypes,{"Column1", "Column2"}),
            #"Merged Columns" = Table.CombineColumns(#"Filled Down", {"Column1", "Column2", "Column3"}, Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Scalone"),
            #"Replaced Value" = Table.ReplaceValue(#"Merged Columns",",,","",Replacer.ReplaceText,{"Scalone"}),
            Tbl1 = Table.Transpose(#"Replaced Value"),
            Tbl2 = Table.RemoveFirstN(RemOtherCols , 3),
            #"Appended Query" = Table.Combine({Tbl1, Tbl2}),
            #"Promoted Headers" = Table.PromoteHeaders(#"Appended Query", [PromoteAllScalars=true]),
            #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Salesman", "Country", "Region"}, "Attribute", "Value"),
            #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Product", "Year", "Month"}),
            #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Salesman", type text}, {"Country", type text}, {"Region", type text}, {"Product", type text}, {"Month", type text}, {"Year", Int64.Type}, {"Value", type number}}),
            FileName = Table.AddColumn(#"Changed Type", "File name", each fileName, type text)
        in
            FileName,


    Source = Folder.Files("C:\PQ\Example\"),
    #"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".xlsx"),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content", "Name"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each fnTransform([Content],[Name])),
    Append = Table.Combine(#"Added Custom"[Custom])
in
    Append
 
You must log in or register to reply here.
Back
Top