Forum Discussion
Unpivot non-consecutive columns
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"
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"
So merge is the only way to pivot those two colums side by side. And we can merge the same table with it but the OK is greyed out in the join window.
Before I accept it as solution.
I did in a similar way but had to duplicate query then do inner join.
What I don't understand is how does the solution where deleting $ columns first then unpivoting it and deleting Hrs columns next then unpivoting it. Later merge both tables work when new data (MML 05 Hrs MML 05 $) is added to it.
I can see the column headings are hardcoded in the code however, when new data is added to the right and refreshed it works perfectly I don't understand how? MML 05 Hrs and MML 05 $ are removed and unpivoted.
PowerBI_Query I can see the column headings are hardcoded in the code however, when new data is added to the right and refreshed it works perfectly I don't understand how?
- we know there are columns which will be constant axis (Category, Sub Category).
In
#"Filtered Rows"= Table.SelectRows(#"Transposed Table", each ([Column1] = "Category" or [Column1] = "Sub Category") or Text.Contains([Column1], "Hrs"))and
Custom2= Table.SelectRows(#"Transposed Table", each ([Column1] = "Category" or [Column1] = "Sub Category") or Text.Contains([Column1], "$"))only Column1 and Column2 is Harcoded (cause those are our constant axis) but the other elemets are not, SO whenever new data gets added, it will get correctly filtered dynamically through Text.Contains([Column1], "Hrs" and Text.Contains([Column1], "$").
I don't think you can unpivot data on different partition (Hrs, $) i the same step at once. Therefore, unpivoted speratly after dynamic filtering and merged back.
Hardcoding was related to my solution where I first duplicate the query. In $ query I remove Hrs and unpivot other columns.
In Hrs query, I remove $ columns and unpivot other columns, doing this way woud hardcode {MML 01 $ MML 02 $ MML 03 $ MML 04 $} and {MML 01 Hrs MML 02 Hrs MML 03 Hrs MML 04 Hrs}
Then I merge $ query and Hrs query. Now when I add new data MML 05 Hrs and MML 05 $ it still works I don't understand how?
PowerBI_Query you really don't need to duplicate query (one for Hrs and one for $). PQ can self join any previous steps and that is what I did.