Hi,
I am trying to Transpose data using DAX rather than the power query method but finding it tricky to convert a single row into multiple columns whilst turning several columns into rows.
As an example my table is as follows
| Location | A | B | C | D | E |
| City1 | 1 | 2 | 3 | 4 | 5 |
| City2 | 6 | 7 | 8 | 9 | 10 |
| City3 | 11 | 12 | 13 | 14 | 15 |
| City4 | 16 | 17 | 18 | 19 | 20 |
I would like to convert it to
| | City1 | City2 | City3 | City4 |
| A | 1 | 6 | 11 | 16 |
| B | 2 | 7 | 12 | 17 |
| C | 3 | 8 | 13 | 18 |
| D | 4 | 9 | 14 | 19 |
| E | 5 | 10 | 15 | 20 |
I managed to convert the columns into rows using the UNION and SELECTCOLUMNS but don't know how to transpose the location field into columns.
The formula I have used so far is as follows
NewTable =
UNION (
SELECTCOLUMNS (
'[Table1]',
"Location",'Table1'[Location],
"NewColumnName", "[A]",
"Value", 'Tavble1'[A]
),
SELECTCOLUMNS (
'[Table1]',
"Location",'Table1'[Location],
"NewColumnName", "[B]",
"Value", 'Tavble1'[B]
))
Any help would be appreciated.
