Create new table by transposing/Crosstab a data table

[vibhoryadav23]

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.

[amitchandak · Accepted solution]

@vibhoryadav23 ,

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

https://radacad.com/pivot-and-unpivot-with-power-bi

[vibhoryadav23]

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?

[amitchandak]

@vibhoryadav23 , Right click on table in power query and duplicate. Or Copy paste , then unpivot