Unpivot a matrix with multiple fields on columns in Power Query

I had to do this for a client the other day, and I realized I hadn’t blogged about it. Let’s say you need to include data in a Power BI model, but the only source of the data is a matrix that is output from another system. And that matrix has multiple fields populating the columns. An example of this is below. The matrix has fiscal year and product category on columns, vertical on rows, and the profit metric populating the values.

A matrix in Excel with Fiscal Year and Product Category on Columns and Vertical on Rows with Profit shown in the Measures.

You may know about the wonderful unpivot functionality in Power Query, but to handle this matrix, you first need to Transpose.

The steps to turn this matrix into a table are:

  1. Transpose the query.
  2. Remove the last column that contains the vertical totals.
  3. Filter out the “Totals” value in the second column, which contains the product categories.
  4. Use the first row as headers.
  5. Select the Fiscal Year, Product Category, and Metric columns. Select Unpivot Other Columns.
  6. Rename the Attribute column to Verticals.

Transposing a table changes categories into rows.

A query with fiscal year and product category on columns and vertical on rows is transposed. It becomes a query with fiscal year in the first column, product category in the second column, and the verticals become columns instead of rows.
Transposing a query in Power Query

We don’t need the totals columns or rows because Power BI can recalculate those, and we don’t want to double-count profits.

In order to unpivot, we need to promote the first row to column headers, so the first column is labeled Fiscal Year and the fourth column is labeled Vertical Z.

The first three columns are as they should be, but the verticals need to change from columns to rows. This is accomplished by unpivoting. But you only want to unpivot the verticals and leave the fiscal year, product category, and metric columns as they are.

Then make sure column names are user-friendly, and it’s finished. You may also opt to remove the Metric column and rename the value column, if there is only one metric.

A query with 4 columns: fiscal year, product category, verticals, profit
The finished product after transposing and unpivoting

Share

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?