Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not 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 demo 

    Not 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"