Unpivot

Most of the time Power Query is used to prepare data to use in pivot tables. Strangely one of the most powerful features of Power Query is unpivot.

When tabular data is unpivoted, columns holding column data headings and cell data are transformed into Attribute (Data headings) and value (Cell data) pairs.

To unpivot tabular data, open a new file and navigate to Data menu tab, then select Get Data - From File - From Workbook.

Select the Workbook with the data to unpivot.

Select the Worksheet that holds the data to unpivot. Click on the Transform Data button.

The data will be imported. Select the columns that you don’t want to unpivot, by clicking on the column headings using Ctrl + left mouse click. Click on the Transform menu tab and then select Unpivot Columns - Unpivot Other Columns.

The columns that were not selected will be unpivoted into an attribute column and a value column.

On the Home menu tab, click on Close & Load. The unpivoted data will be loaded back into the workbook.

The unpivoted data can be re-pivoted, using the Table Design - Summarize with PivotTable command. This allows the full functionality of Excel PivotTables to be used with the data.

Notice that no values for December are imported. That is because the empty cells in the source workbook are interpreted as nulls and not imported back into Excel. Null values can be converted to zeros in Power Query before the unpivot step, if want to import them.

Tips

Column headings

The column headings of attribute and value can be renamed as part of the unpivot step.

Once the step has been created, click on the formula bar and change the heading values.
They will originally appear like this:

= Table.UnpivotOtherColumns(#"Changed Type", {"Account Id", "Account Description"}, "Attribute", "Value")

The last values in the function can be updated from Attribute to Month and Value to Amount.

= Table.UnpivotOtherColumns(#"Changed Type", {"Account Id", "Account Description"}, "Month", "Amount")

Dates

The column headings of the month name that become the Attribute column when unpivoted, can be converted to a date in Power Query. This will provide access to the date functions in Excel when the data is imported.

In Power Query, after the data has been unpivoted, insert a custom column.
Navigate to Add Column on the top menu and click on Custom Column.
Add a formula to create a date using the text month name.

=Date.FromText("01 " & [Month] & " 2025")

Click on the OK button.
The original column can be removed.

As the column is now a date, the full range of date functionality is available to use in Excel.