Forum Discussion
Create new table by transposing/Crosstab a data table
Hi,
I need to crosstab/transpose data from one table to create a new table as shown in example below.
Original Table:
| User | Week | Status |
| A1 | Week1 | Active |
| A1 | Week2 | Inactive |
| A1 | Week3 | Deleted |
| A2 | Week1 | Active |
| A2 | Week2 | Active |
| A2 | Week3 | Deleted |
| A3 | Week1 | Active |
| A3 | Week2 | Active |
| A3 | Week3 | Active |
| A4 | Week3 | Idle |
| A5 | Week2 | Active |
| A5 | Week3 | Active |
New Table/Changed format/Desired output:
| User | Week1 | Week2 | Week3 |
| A1 | Active | Inactive | Deleted |
| A2 | Active | Active | Deleted |
| A3 | Active | Active | Active |
| A4 | Idle | ||
| A5 | Active | Active |
Can anyone help me with the same? Thanks in advance.
In matrix viusal , Week on column , Max of Status measure as value
Or if need a new table, Unpivot in Power Query- Take max of status -> If it does not give Option, After table is created you will see List.Count in script(formula), Make that List.Max
5 Replies
In matrix viusal , Week on column , Max of Status measure as value
Or if need a new table, Unpivot in Power Query- Take max of status -> If it does not give Option, After table is created you will see List.Count in script(formula), Make that List.Max
Hi amitchandak ,
Thanks for your response. The second option works but it changes my current data. Is it possible to create a new table instead of changing the existing table?
vibhoryadav23 , Right click on table in power query and duplicate. Or Copy paste , then unpivot
The Power Query unpivot approach is correct for reshaping data at the model level. One thing to note: unpivoting changes your data structure permanently, which can affect other visuals and measures that depend on the original wide format.
If the goal is to display the data in a crosstab layout (User as rows, Week as columns, Status as values) without restructuring the underlying table, the Matrix visual handles this directly put User in Rows, Week in Columns, Status in Values. No unpivoting needed.
For end-users who need to swap which dimension goes in rows vs columns themselves after publishing, Flexa Tables on AppSource adds this flexibility to the published report
This is a classic pivot/crosstab use case. Use User as the row key, Week as the column headers, and Status as the value field, with an aggregation like First (or Max if there's only one status per user/week). If you share which tool you're using (SQL, Excel, Power Query, Alteryx, etc.), we can provide the exact solution.
