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.
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:
- Transpose the query.
- Remove the last column that contains the vertical totals.
- Filter out the “Totals” value in the second column, which contains the product categories.
- Use the first row as headers.
- Select the Fiscal Year, Product Category, and Metric columns. Select Unpivot Other Columns.
- Rename the Attribute column to Verticals.
Transposing a table changes categories into rows.
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.
