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"
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
Custom1It worked in excel.
How do I update the file path in Power BI? in the below step "C:\Power BI\Unpivot.xlsx"Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
I have been trying to understand the query by breaking down into small steps.
However, I am unable to undestand the nested let funtion significance. If you don't mind could you explain it?
Custom1 step has filtered MML 1 MML 2 MML 3 MML 4 dynamically then a table is created with the headings from List.FirstN and concating {"MML","HRS","$"}
But why #table after in?
What is the pagesize in List.Spilt mean and List.Zip has no arguments so how col was refered in List.Zip({List.Split(List.Skip(_,2),2),col})
and the user define funtion after the go to operator (x,y)=>List.FirstN(x,2)&{y{1}}&y{0}))
Do 0, 1 and 2 refer to positions I guess. But how do I visulize it? I get it that the values are being placed under the table headings. How does the new table get filled, I did not understand.I could not perform List.Zip steps on Table.ToRows could you spilt the original query into small steps as below?
I managed to understad most of it except the List.TransformMany funtion. I get errors in this step could you explain the last step? how resultTransform argument works.
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.
