Forum Discussion
how to reshape data
Hi,
I am new to data reshaping. I have attached a link to a sample spreadsheet which has a raw data worksheet which I'd like to reshape to look like the second worksheet, Desired Output. I don't care whether it's done in PowerQuery or Dax, but would like to understand how to solve this. My database has MANY more data points, but this will serve as the example.
I can inport the raw data worksheet into PowerBI, but I can't seem to figure out how to get the Desired OUtput data structure.
https://1drv.ms/x/s!AodJ9zqlTT5Ls_Rzp95r6TOV2zZE8Q?e=JHbanq
Thank you,
Use this please
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Role]), "Role", "Participant"), Custom1 = Table.TransformColumnNames(#"Pivoted Column", each Text.Proper(_)) in Custom1
5 Replies
A simple pivot should do the trick
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Pivoted Column" = Table.Pivot(Source, List.Transform(List.Distinct(Source[Role]), each Text.Proper(_)), "Role", "Participant") in #"Pivoted Column"Thank you, Vijay. Where does this code go?
Actually, what I get when I use that in PowerQuery is this:
Is there a way to fill in the names into the appropriate roles?
Thank you
Use this please
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Role]), "Role", "Participant"), Custom1 = Table.TransformColumnNames(#"Pivoted Column", each Text.Proper(_)) in Custom1
