how to reshape data

[davidv64]

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,

[Vijay_A_Verma]

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"

[davidv64]

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

[Vijay_A_Verma · Accepted solution]

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

[davidv64]

Very nice!!  Now I need to dissect your code and understand it .... but it works!

[davidv64]

Thank you, Vijay. Where does this code go?