Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
davidv64
New Member

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,

 

1 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

View solution in original post

5 REPLIES 5
Vijay_A_Verma
Super User
Super User

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"

Actually, what I get when I use that in PowerQuery is this:

 

davidv64_0-1689996377892.png

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

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

Thank you, Vijay. Where does this code go?

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.