The attached data has two Rows of headers. i need to get the top row of headers unpivoted so they are on each row and the top Header is Hours / Contacts.
I then plan to use List.Zip from this EDNA video ( https://www.youtube.com/watch?v=U1O5LfMZP0s) to Unpivot the two Columns.
How would I go about the initial Unpivot so that Hours / Contacts are the Headers?
Thank You
UnPivot Multiple Columns.xlsm (32.1 KB)
Hello @AllisterB,
Thank You for posting your query onto the Forum.
Is this the type of results you’re looking for, for both the type of worksheets provided into the Excel file by you? Below is the screenshot provided for the reference -
I’ve done the transformations for both the type of worksheets. You can go through the transformative steps that I’ve done. Please give a shout if in case you find it difficult to understand any of the steps.
Hoping you find this useful and meets your requirements that you’ve been looking for. 
Note:
1. In any of the transformations, I have not made use of M Code. I’ve simply used the available options into the Query Editor to do this transformations.
2. Transformations performed on both the worksheets are almost similar.
3. At first I performed the transformations on Worksheet No. 2 and than onto the Worksheet No. 1. Since Worksheet No. 2 was placed first into the Excel File. So if you get/understand the transformation steps for Worksheet No. 2 than you’ll also be able to understand the transformations for Worksheet No. 1.
Thanks and Warm Regards,
Harsh
UnPivot Multiple Columns - Harsh.xlsm (32.1 KB)
Power Query Transformations - Harsh.pbix (39.3 KB)
Hello @AllisterB,
There’s also an alternative where you’ll absolutely not be required to perform the intensive transformative steps like I’ve performed in my file.
You just need to copy and paste the M Code from my file to yours and automatically the transformed data will be loaded into your PBIX file. Below are the steps suggested for the reference -
Step 1: In the Power Query, under the “New Source” select the “Blank Query” option.
Step 2: Now, go to the “Advanced Editor” option and paste the below provided query into it.
let
Source = Excel.Workbook(File.Contents("C:\Users\har\Downloads\UnPivot Multiple Columns.xlsm"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type any}, {"Column5", type text}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", Int64.Type}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column2", "Column4"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
#"Filled Down" = Table.FillDown(#"Removed Top Rows",{"Column1", "Column3"}),
#"Replaced Value" = Table.ReplaceValue(#"Filled Down"," ","",Replacer.ReplaceText,{"Column5"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Hours","A-SIL Hours",Replacer.ReplaceValue,{"Column6"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Contacts","A-SIL Contacts",Replacer.ReplaceValue,{"Column7"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Hours","B-SIL Hours",Replacer.ReplaceValue,{"Column8"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","Contacts","B-SIL Contacts",Replacer.ReplaceValue,{"Column9"}),
#"Removed Columns1" = Table.RemoveColumns(#"Replaced Value4",{"Column10"}),
#"Replaced Value5" = Table.ReplaceValue(#"Removed Columns1","Hours","T-SIL Hours",Replacer.ReplaceValue,{"Column11"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","Contacts","T-SIL Contacts",Replacer.ReplaceValue,{"Column12"}),
#"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","Hours","S-SIL Hours",Replacer.ReplaceValue,{"Column13"}),
#"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","Contacts","S-SIL Contacts",Replacer.ReplaceValue,{"Column14"}),
#"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","Hours","C-SIL Hours",Replacer.ReplaceValue,{"Column15"}),
#"Replaced Value10" = Table.ReplaceValue(#"Replaced Value9","Contacts","C-SIL Contacts",Replacer.ReplaceValue,{"Column16"}),
#"Removed Top Rows1" = Table.Skip(#"Replaced Value10",1),
#"Promoted Headers1" = Table.PromoteHeaders(#"Removed Top Rows1", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Branch Name", type text}, {"Key Staff", type text}, {"StaffType", type text}, {"A-SIL Hours", Int64.Type}, {"A-SIL Contacts", Int64.Type}, {"B-SIL Hours", Int64.Type}, {"B-SIL Contacts", Int64.Type}, {"T-SIL Hours", Int64.Type}, {"T-SIL Contacts", Int64.Type}, {"S-SIL Hours", Int64.Type}, {"S-SIL Contacts", Int64.Type}, {"C-SIL Hours", Int64.Type}, {"C-SIL Contacts", Int64.Type}}),
#"Removed Columns2" = Table.RemoveColumns(#"Changed Type1",{"A-SIL Hours", "B-SIL Hours", "T-SIL Hours", "S-SIL Hours", "C-SIL Hours"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns2", {"Branch Name", "Key Staff", "StaffType"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Attributes"}, {"Value", "Values"}})
in
#"Renamed Columns"
Now, once the query is loaded and the table is generated. Rename the table from “Query1” to “Contacts - As Per Sheet 1”. Since the above query provided is for the transformations performed onto the Worksheet No. 1
Step 3: Now again, go to the “Advanced Editor” option and paste the below provided query into it.
let
Source = Excel.Workbook(File.Contents("C:\Users\har\Downloads\UnPivot Multiple Columns.xlsm"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type any}, {"Column5", type text}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", Int64.Type}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column2", "Column4"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
#"Filled Down" = Table.FillDown(#"Removed Top Rows",{"Column1", "Column3"}),
#"Replaced Value" = Table.ReplaceValue(#"Filled Down"," ","",Replacer.ReplaceText,{"Column5"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Hours","A-SIL Hours",Replacer.ReplaceValue,{"Column6"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Contacts","A-SIL Contacts",Replacer.ReplaceValue,{"Column7"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Hours","B-SIL Hours",Replacer.ReplaceValue,{"Column8"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","Contacts","B-SIL Contacts",Replacer.ReplaceValue,{"Column9"}),
#"Removed Columns1" = Table.RemoveColumns(#"Replaced Value4",{"Column10"}),
#"Replaced Value5" = Table.ReplaceValue(#"Removed Columns1","Hours","T-SIL Hours",Replacer.ReplaceValue,{"Column11"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","Contacts","T-SIL Contacts",Replacer.ReplaceValue,{"Column12"}),
#"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","Hours","S-SIL Hours",Replacer.ReplaceValue,{"Column13"}),
#"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","Contacts","S-SIL Contacts",Replacer.ReplaceValue,{"Column14"}),
#"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","Hours","C-SIL Hours",Replacer.ReplaceValue,{"Column15"}),
#"Replaced Value10" = Table.ReplaceValue(#"Replaced Value9","Contacts","C-SIL Contacts",Replacer.ReplaceValue,{"Column16"}),
#"Removed Top Rows1" = Table.Skip(#"Replaced Value10",1),
#"Promoted Headers1" = Table.PromoteHeaders(#"Removed Top Rows1", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Branch Name", type text}, {"Key Staff", type text}, {"StaffType", type text}, {"A-SIL Hours", Int64.Type}, {"A-SIL Contacts", Int64.Type}, {"B-SIL Hours", Int64.Type}, {"B-SIL Contacts", Int64.Type}, {"T-SIL Hours", Int64.Type}, {"T-SIL Contacts", Int64.Type}, {"S-SIL Hours", Int64.Type}, {"S-SIL Contacts", Int64.Type}, {"C-SIL Hours", Int64.Type}, {"C-SIL Contacts", Int64.Type}}),
#"Removed Columns2" = Table.RemoveColumns(#"Changed Type1",{"A-SIL Contacts", "B-SIL Contacts", "T-SIL Contacts", "S-SIL Contacts", "C-SIL Contacts"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns2", {"Branch Name", "Key Staff", "StaffType"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Attributes"}, {"Value", "Values"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Branch Name", "Key Staff", "StaffType"}, #"Contacts - As Per Sheet 1", {"Branch Name", "Key Staff", "StaffType"}, "Contacts - As Per Sheet 1", JoinKind.LeftOuter),
#"Expanded Contacts - As Per Sheet 1" = Table.ExpandTableColumn(#"Merged Queries", "Contacts - As Per Sheet 1", {"Values"}, {"Values.1"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Contacts - As Per Sheet 1",{{"Values.1", "Contacts"}, {"Values", "Hours"}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns1", "Attributes", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attributes.1", "Attributes.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attributes.1", type text}, {"Attributes.2", type text}}),
#"Removed Columns3" = Table.RemoveColumns(#"Changed Type2",{"Attributes.2"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns3",{{"Attributes.1", "Attributes"}})
in
#"Renamed Columns2"
Now, once the query is loaded and the table is generated. Rename the table from “Query1” to “Raw Data - As Per Sheet 1”. Again, the above query provided is for the transformations performed onto the Worksheet No. 1
Step 4: Finally, un-check the option of “Enable Load” for the table “Contacts - As Per Sheet 1”.
Note:
1. Just under the first line “Source” you’ll have to change the file location i.e. mention the file path where your Excel file is located. Apart from this, everything remains the same.
2. Once you complete the aforementioned steps for Worksheet No. 1. Follow the same steps for Worksheet No. 2.
3. So firstly, you’ll be required to copy the M Code of “Contacts” part and paste it into the Advanced Editor and than you’ll have to copy and paste the M Code for “Raw Data” part.
Hoping this is way easier for you than to go through and perform tedious transformative steps like I’ve performed in my file. And also hoping you’ll find this useful.
Thanks and Warm Regards,
Harsh
Hi Harsh
Thank you for all your efforts.
I am trying to do it by Merging the two Headers Rows - after doing this I 'll try your solution.
Its good to know from your solution that it is possible.
kind regards
Allister
Hello @AllisterB,
You’re Welcome.
I’m glad that I was able to assist you.
Thanks and Warm Regards,
Harsh
I think I have managed to do this by Merging the Top two rows as the Data before my Added Custom looks ok.
But then I get Errors in the data related to S-SIL
Also, the data in the output table does not agree to that in the Input table eg capetown|Hilda|T-SIL should be 1075 hours and 22 Contacts.
What has gone wrong from the Added Custom point onward?
Any ideas?
UnPivot Multiple Columns.xlsm (38.4 KB)
Hello @AllisterB,
I’m not proficient with the M Language functions and therefore I try to make minimal use of M functions while performing the transformations and make the optimal use of options available into the Power Query.
I hope @Melissa can help you with this. Till then you can try using the solution which I’ve provided above.
Thanks and Warm Regards,
Harsh
Hi @AllisterB,
First kudos you were so incredibly close to solving this yourself, great job 
How did I discover what was wrong with your code?
I started by examining the error message
This talks about field access to a type number 
That can’t be right so then I looked over your M code and found the culprit - a missing comma
Next just because it’s fun I made some other changes to your code:
Added a fxCleanText function
(myText) => try Text.Proper( Text.Trim( Text.Clean( Text.From(myText) ))) otherwise myText
So here’s the full revised M query:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
RemoveTopRows = Table.Skip(Source,1),
RemoveColumns = Table.RemoveColumns(RemoveTopRows,{"Column2", "Column4"}),
CleanText = Table.TransformColumns(RemoveColumns,{{"Column1", fxCleanText, type text}, {"Column3", fxCleanText, type text}, {"Column5", fxCleanText, type text}}),
FillDown = Table.FillDown(CleanText,{"Column1", "Column3"}),
Transpose = Table.FillDown( Table.Transpose(FillDown),{"Column1"}),
RemoveNull = Table.SelectRows(Transpose, each ([Column2] <> null)),
MergeColumns = Table.CombineColumns(RemoveNull,{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
CleanedText = Table.TransformColumns(MergeColumns,{{"Merged", fxCleanText, type text}}),
TransposeBack = Table.PromoteHeaders( Table.Transpose(CleanedText), [PromoteAllScalars=true]),
Custom = Table.ExpandListColumn( Table.SelectColumns(Table.AddColumn(TransposeBack, "Custom", each List.Zip( { {"A-SIL", "B-SIL", "T-SIL", "S-SIL", "C-SIL"}, {[#"A-Sil Hours"], [#"B-Sil Hours"], [#"T-Sil Hours"], [#"S-Sil Hours"], [#"C-Sil Hours"]}, {[#"A-Sil Contacts"], [#"B-Sil Contacts"], [#"T-Sil Contacts"], [#"S-Sil Contacts"], [#"C-Sil Contacts"]} })),{"Branch Name", "Key Staff", "Staff Type", "Custom"}), "Custom"),
ExtractValues = Table.SplitColumn( Table.TransformColumns(Custom, {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}), "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"City", "Hours", "Contacts"}),
ChType = Table.TransformColumnTypes(ExtractValues,{{"Branch Name", type text}, {"Key Staff", type text}, {"Staff Type", type text}, {"City", type text}, {"Hours", Int64.Type}, {"Contacts", Int64.Type}})
in
ChType
also checked the results for capetown|Hilda|T-SIL and they are 1075 hours and 22 Contacts
Here’s your sample file. UnPivot Multiple Columns (1).xlsm (161.0 KB)
I hope this is helpful.
Thank you Melissa
Thank you too for your encouragement although the video you did https://www.youtube.com/watch?v=U1O5LfMZP0s was very very good
I have a concern though
The items such as A-SIL etc may change… In that Next time I get the source data the current items may or may not be there and new ones might exist. However the M code is hardcoded… How can the code be dynamic
… Is it even possible.
Kind Regards
Allister
@AllisterB it’s always a best practice to mention those types of requirements as early on as possible.
Let’s sort that too so I’ve stepped through the code to see where I can dynamically retrieve those attributes and the RemoveNull step is perfectly suited for that job
I wrote this line of M code by hand in the Advanced Editor window, to avoid PQ from helping me and referencing this manual step in places where I don’t need it.
AttributeList = List.Distinct( List.RemoveNulls( RemoveNull[Column1] ))
Next I updated the List.Zip parameters and replaced the hard coded {“A-SIL”, “B-SIL”, “T-SIL”, “S-SIL”, “C-SIL”} with your new dynamic AttributeList and adjusted that for the Hours and Contacts as well
List.Zip( { AttributeList, Record.FieldValues( Record.SelectFields( _, List.Transform( AttributeList, each Text.Proper(Text.From(_))&" Hours"))), Record.FieldValues( Record.SelectFields( _, List.Transform( AttributeList, each Text.Proper(Text.From(_))&" Contacts"))) })
Updated full M query here:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
RemoveTopRows = Table.Skip(Source,1),
RemoveColumns = Table.RemoveColumns(RemoveTopRows,{"Column2", "Column4"}),
CleanText = Table.TransformColumns(RemoveColumns,{{"Column1", fxCleanText, type text}, {"Column3", fxCleanText, type text}, {"Column5", fxCleanText, type text}}),
FillDown = Table.FillDown(CleanText,{"Column1", "Column3"}),
Transpose = Table.FillDown( Table.Transpose(FillDown),{"Column1"}),
RemoveNull = Table.SelectRows(Transpose, each ([Column2] <> null)),
AttributeList = List.Distinct( List.RemoveNulls( RemoveNull[Column1] )),
MergeColumns = Table.CombineColumns(RemoveNull,{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
CleanedText = Table.TransformColumns(MergeColumns,{{"Merged", fxCleanText, type text}}),
TransposeBack = Table.PromoteHeaders( Table.Transpose(CleanedText), [PromoteAllScalars=true]),
Custom = Table.ExpandListColumn( Table.SelectColumns(Table.AddColumn(TransposeBack, "Custom", each List.Zip( { AttributeList, Record.FieldValues( Record.SelectFields( _, List.Transform( AttributeList, each Text.Proper(Text.From(_))&" Hours"))), Record.FieldValues( Record.SelectFields( _, List.Transform( AttributeList, each Text.Proper(Text.From(_))&" Contacts"))) })),{"Branch Name", "Key Staff", "Staff Type", "Custom"}), "Custom"),
ExtractValues = Table.SplitColumn( Table.TransformColumns(Custom, {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}), "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"City", "Hours", "Contacts"}),
ChType = Table.TransformColumnTypes(ExtractValues,{{"Branch Name", type text}, {"Key Staff", type text}, {"Staff Type", type text}, {"City", type text}, {"Hours", Int64.Type}, {"Contacts", Int64.Type}})
in
ChType
I hope this is helpful
apologies for not advising of the requirement earlier I mistakingly thought a stepwise approach would be easier (for me)
All good.
Made a slight adjustment above
Hi
Could someone explain how I might install eth Custome function in post 8 above.
I have never used Custom Function before.
I have tried to right-click on the query - but “Create Function” does not show.
Thank You
Allister
Hi @AllisterB,
The Custom Function is just another Query, the one I created was called fxCleanText
You can reference Custom Functions by their name
If you open the Advanced Editor of that Query you can replace all you see there with this M code:
(myText) => try Text.Proper( Text.Trim( Text.Clean( Text.From(myText) ))) otherwise myText
Maybe this YT video will intrest you
I hope this is helpful.
i am a bit lost about how to get eth custom function to work …
Allister
UnPivot Multiple Columns.xlsm (40.3 KB)
Here you go
UnPivot Multiple Columns (2).xlsm (40.5 KB)