Transpose table in report visual

[webportal]

This is how my table looks like:

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!

[v-yulgu-msft]

Hi @webportal,

Agree with supder, the current table structure doesn't match the matrix visual. To achieve your goal, you have to unpivot columns, as it is not available to do that using DAX, as supder mentioned, the best choice is duplicating your source table.

Best regards,
Yuliana Gu

[webportal]

Hi,

I know it doens't match the matrix. The problem is that by duplicating and unpivoting the table, I'll have two tables which are not synchronized. That is, changes in the original table will not be reflected on the tranposed table.

[spuder · Accepted solution]

The source data should always be equal. But you are right. Transformation steps won't be duplicated.

If this is your intenstion use reference (one below) instead of duplicate.

Then the source of your second query isn't the original source (database?) but the first query.

Every transformation step will be "forwarded" to your new query. So you'll keep all your changes and only unpivot the second query.

[spuder]

hi @webportal

i think this is not possible. Maybe it helps when you duplicate your source in Query Editor. Then you can use one to unpivot and the other one default.

If you have performance problems try not to duplicate but to reference to the default source.

[webportal]

Hello @spuder

Thanks for your help. Actually, if I duplicate then the values will not be in synch, right?

[spuder]

@webportal

it will. The duplicate will be handled as a normal query and will be updated with every refresh.

[webportal]

Ok, explain 🙂

[spuder]

as I said.

Open the query editor and duplicate your query. Then you can the original one use for the visuals where this format is neccessary and the duplicate you can unpivot and use this one in the visuals where unpivoted data where needed.

[webportal]

That's great, but the two tables are not in synch.