Forum Discussion
Solved
Unpivot non-consecutive columns
I am able to unpivot consecutive columns by tweaking the M code but having trouble when unpivoting non-consecutive columns as shown below. I need to make the column names dynamic. So that when new da...
PowerBI_Query can you try this
let Source = Excel.Workbook(File.Contents("C:\Users\user1\Desktop\Unpivot.xlsx"), null, true), #"Before unpivot_Sheet" = Source{[Item="Before unpivot",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(#"Before unpivot_Sheet", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Category", type text}, {"Sub Category", type text}, {"MML 01 Hrs", Int64.Type}, {"MML 01 $", Int64.Type}, {"MML 02 Hrs", Int64.Type}, {"MML 02 $", Int64.Type}, {"MML 03 Hrs", Int64.Type}, {"MML 03 $", Int64.Type}, {"MML 04 Hrs", Int64.Type}, {"MML 04 $", Int64.Type}}), Custom1 = Table.DemoteHeaders(#"Changed Type"), #"Changed Type1" = Table.TransformColumnTypes(Custom1,{{"Column1", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}}), #"Transposed Table" = Table.Transpose(#"Changed Type1"), #"Filtered Rows" = Table.SelectRows(#"Transposed Table", each ([Column1] = "Category" or [Column1] = "Sub Category") or Text.Contains([Column1], "Hrs")), #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","Hrs","",Replacer.ReplaceText,{"Column1"}), #"Transposed Table1" = Table.Transpose(#"Replaced Value"), #"Promoted Headers1" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]), #"Unpivoted Other Columns" = Table.Distinct(Table.UnpivotOtherColumns(#"Promoted Headers1", {"Category", "Sub Category"}, "MML", "HRS")), Custom2 = Table.SelectRows(#"Transposed Table", each ([Column1] = "Category" or [Column1] = "Sub Category") or Text.Contains([Column1], "$")), #"Replaced Value1" = Table.ReplaceValue(Custom2,"$","",Replacer.ReplaceText,{"Column1"}), #"Transposed Table2" = Table.Transpose(#"Replaced Value1"), #"Promoted Headers2" = Table.PromoteHeaders(#"Transposed Table2", [PromoteAllScalars=true]), #"Unpivoted Other Columns1" = Table.Distinct(Table.UnpivotOtherColumns(#"Promoted Headers2", {"Category", "Sub Category"}, "MML", "$")), #"Merged Queries" = Table.NestedJoin(#"Unpivoted Other Columns", {"Category", "Sub Category", "MML"}, #"Unpivoted Other Columns1", {"Category", "Sub Category", "MML"}, "Unpivoted Other Columns1", JoinKind.LeftOuter), #"Expanded Unpivoted Other Columns1" = Table.ExpandTableColumn(#"Merged Queries", "Unpivoted Other Columns1", {"$"}, {"$"}) in #"Expanded Unpivoted Other Columns1"
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Custom1 = let col= List.Transform(List.Alternate(List.Skip(Table.ColumnNames(Source),2),1,1),each Text.Replace(_," $","")) in #table(List.FirstN(Table.ColumnNames(Source),2)&{"MML","HRS","$"},List.TransformMany(Table.ToRows(Source),each List.Zip({List.Split(List.Skip(_,2),2),col}),(x,y)=>List.FirstN(x,2)&{y{1}}&y{0}))
in
Custom1
This is how I visualize it could you confirm if this is right? Zip creates 4 lists in a list for each list in Table.ToRows step. (x,2), y{0} and y{1} did I get them right?
yes，this is it.