Put values in different rows into two columns

[trent-lim]

Hi,

I have an excel spreadsheet as my dataset, that looks like this:

DateTime	Type	Count
1/12/2021 10:00:00 am	NS	597
1/12/2021 10:00:00 am	Su	494
1/12/2021 11:00:00 am	NS	658
1/12/2021 11:00:00 am	Su	553

And I want each date to have its own row, and each type to have its own column like this:

DateTime	NS	Su
1/12/2021 10:00:00 am	597	494
1/12/2021 11:00:00 am	658	553

How would I do this with power query? Thanks in advance

[Ashish_Mathur]

Hi,

This screenshot should say it all

[amitchandak · Accepted solution]

@trent-lim , Pivot the last two column in power query

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

Or create measure like these and use in table visual with date

Ns = countx(filter(Table, Table[Type] ="Ns"), [Count])

Su= countx(filter(Table, Table[Type] ="Su"), [Count])