Forum Discussion
How to unpivot 3 headers with a two sidebar
I have an excel and the data inside are desgined to be printed in excel so I want to rework on power query. As you can see in the photo there are 3 headers and also 2 "header" in the sidebar. I have tried so many step to use unpivot, transpose, merge etc without getting to work. Could someone please help what would be the step to get this done?
2 Replies
- AnonymousNot applicable
You want to transpose, fill down, add a column for pivoting and put it at the front.
Remove COlumn1 and 2, transpose back, promote header and unpivot other column.
you can now split your pivoted column back to their original values
Sample code is below as a demoNot the simplest transform, but it gets the job done.
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Transposed Table" = Table.Transpose(Source), #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}), AddPivotHeader = Table.AddColumn(#"Filled Down", "PivotHeader", each [Column1] & "-" & [Column2]), #"Removed Columns" = Table.SelectColumns(AddPivotHeader, {"PivotHeader"} & List.Select( Table.ColumnNames(AddPivotHeader), each not List.Contains({"Column1","Column2","PivotHeader"}, _))), #"Transposed Table1" = Table.Transpose(#"Removed Columns"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Column1"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"FirstRow", "SecondRow"}) in #"Split Column by Delimiter" - amitchandakSuper User
Refer
https://radacad.com/pivot-and-unpivot-with-power-bi
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
Thanks.
My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601