Transpose table in report visual in Power BI Desktop

Question

This is how my table looks like in Power BI Desktop:

In the 1st column, we have the year, and in the n remaining columns the indicators.

I'd like to build a matrix visual with the indicators in the rows and years in columns.

Now, I know I can unpivot all the columns except the year in the query editor. But the structure above is necessary to build charts where the x-axis is the year and the series is only one or two indicators.

Is there a way (a measure, calculated table or other) to build the matrix I need?

Thanks!

Answer 1

If I understand correctly, you want both your original table layout and a new transposed table like this:

You can do that with a new, additional query. Just go into your query editor and right click on your existing query (in the left pane of the screen), and then click Reference. That will create a new query, using the previously existing query as its source.

Then click the Transform tab. Then click the bottom part of the "Use First Row As Headers" button, so that you can then click on "Use Headers as First Row".

Then click on Transpose.

Then click on the top part of the "Use First Row As Headers" button (or click on its bottom part and then "Use First Row As Headers")

Then click on the Home tab and "Close & Apply". This will add a new table to your Power BI data set, from the transposed table.