Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Advocate II
Advocate II

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?1.png 

Super User
Super User



Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.

My Recent Blog -

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.


    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"})
    #"Split Column by Delimiter"

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors