Convert a section in a data set in Power Q into a list format

Hello Douglas,

Thank you for your question. In Power Query, you have a powerful tool called 'Unpivot'.

To convert your data in Power Query from a matrix format (months in rows and costs in columns) into a list format (also known as unpivoting), you can follow these steps:

Load your data into Power Query

In Excel: Select your data → go to the Data tab → click From Table/Range.

Your data currently looks something like this:

Month | Cost A | Cost B | Cost C
Jan | 100 | 200 | 300
Feb | 110 | 210 | 310

To unpivot the cost columns:

Select all the cost columns (e.g., "Cost A", "Cost B", "Cost C").
Right-click on the selected columns and choose Unpivot Columns.
Rename the columns:

Power Query will rename the columns to something like:

Month | Attribute | Value
Jan | Cost A | 100
Jan | Cost B | 200
Jan | Cost C | 300

You can rename:

Attribute → Cost Type
Value → Amount (or whatever fits your context)

Close & Load:

Click Close & Load to return the transformed data to Excel.

I hope this will be helpful to you.

Please let us know if you need some more assistance.

Kind regards
Marius Barnard
STL