An Alternative Method for Unpivoting Double Header Data - Power Query

Today's discussion focuses on a different technique for unpivoting double header data presented in a cross-tabulated format. While there are many ways to achieve this, most involve transposing the table to merge the double headers into one.

UnpivotDoubleHeaders.xlsx

Download XLSX • 19KB

However, in this post, I will demonstrate an alternative method that avoids using transpose.

let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

Cols = {"Product","Year"},

Skip = Table.Skip(Source, each [Column1] is null),

Unpivot = Table.UnpivotOtherColumns(Skip, {"Column1"},"A","Sales"),

AddColumns = Table.SplitColumn(Unpivot,"A", each {_,_}, Cols),

TransformColumns = Table.TransformColumns(AddColumns, List.Transform(Cols, (f) => {f, each Record.Field(Source{List.PositionOf(Cols,f)},_)})),

Result = Table.RenameColumns(TransformColumns, {"Column1","Month"})

in

 Result

The source step assists us in extracting a table into the Power Query Editor.

A list containing 2 items, "Product" and "Year," was created and named Cols.

We use the Table.Skip function to skip header rows in the source table. Instead of specifying 2 rows, we skip the top rows until a non-null item is encountered in Column1.

Apply the Table.UnpivotOtherColumns function to unpivot all columns, excluding Column1.

Column "A" contains header values such as "Column2", "Column3"...

We will use the Table.SplitColumn function to generate the Product and Year columns from column "A". Both columns will contain the same values as in Column "A".

During the TransformColumns step, focus on using the List.Transform function for transformation.

List.Transform(Cols, (f) => {f, each Record.Field(Source{List.PositionOf(Cols,f)},_)})

The List.Transform function iterates over each item in Cols and performs the transformation.

In the first iteration,

f -> refers to "Product"

_ -> refers to each value in the Product column.

The List.PositionOf function provides the position of the item "Product" in the Cols list, which results in 0. This means Source{0} will extract the first row in the Source table as a record.

The headers Column2 and Column3 are found in our Product column, which is _.

We can utilize the Record.Field function to retrieve the values within the record.

The same steps will be applied to the second item in the list, which is Year.

In the final step, we change the name of column1 to Month to achieve our desired outcome.