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 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZS7bhwxDEV/ZbH1BpAo6lXnE1waWzhAkNKA4RT5+8w9HK/tgWFtMYJEjY7IS1KPj+efT6+//zy//Dvl8+X88PfX6d3wQ6ac6zaWNpIWzbax5uFa9LGNbt3YkanM0s7Xy/dU/e4APImQGbCk/ctDP3XbLl3Ryu3yXHJ40nVBgl0SHI2WNI6U1kz/7MJ2lnkKuw7YmA0lZDHzJbJyCD3xZsrJMkJVpCsCKhofS1rDkUmAhldZJm+Ot9qYcRvU6r4OWg7lBMbA5N5jAUgKEDO5b1vSV8CB3PoZXNfhFhnv8rD2cHFmdu9wUacoRw/dPkwNfQPOvNe1h1lHqyh7Ior0r4UMFd3WUNdtT9AnoB2BRsO0wfUW/UKR0DTRMhEy9dpqS0uiTKNDjKbbBdyzS4YKO5PtmnzJLFG0VAqcMm6pGVHRZJraGW0N9Ns7sb0MxD0lnvcehHAVJI3UxhIp2OTlSUHER/ccpT/eYtWC3pp3iEk2wjNeihQFHkWKZYbIxamDO5B0DWr52/sA1HHRNVclytuCpMfG+YI5jvnZIsXEC6YERZuHImstiTh6p0XZyBBKGvNpt36yYx9+VebpICTiWZ27hrBQkhJSX16v/wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, #"Sub Category" = _t, #"MML 01 Hrs" = _t, #"MML 01 $" = _t, #"MML 02 Hrs" = _t, #"MML 02 $" = _t, #"MML 03 Hrs" = _t, #"MML 03 $" = _t, #"MML 04 Hrs" = _t, #"MML 04 $" = _t]),
#"Demoted Headers" = Table.DemoteHeaders(Source),
Cols = Table.ToColumns(#"Demoted Headers"),
#"List of Tables" = let fixed = List.FirstN(Cols,2) in List.Transform(List.Split(List.Skip(Cols,2),2), each Table.PromoteHeaders(Table.FromColumns(fixed & _))),
#"Table Transformation" = let colNames = List.FirstN(Table.ColumnNames(Source),2) in List.Transform(#"List of Tables", each Table.SplitColumn(Table.UnpivotOtherColumns(_, colNames, "attr", "val"), "attr", (col) => Splitter.SplitTextByPositions({0, Text.PositionOf(col, " ", Occurrence.Last)+1})(col), {"MML", "a"})),
Combined = Table.Combine(List.Transform(#"Table Transformation", each Table.Pivot(_, List.Distinct([a]), "a", "val")))
in
Combined