Forum Discussion

DJT0mss's avatar
DJT0mss
New Member
3 years ago
Solved

Pivot rows to column

Hello All, I'm new to power BI, please how can I transform this table

From this -

 

To this - 

Tried pivots but not getting result need. Appreciate the help

Need Help
  • ChrisMendoza's avatar
    ChrisMendoza
    3 years ago

    DJT0mss - There's actually a bit that goes into makeing this work so I'll just include the PBIX so you can step through the 'applied steps'.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjTUVdJRMjTSByIjAyMjpVgdoKARFjFDqJghFjFjJDFjqJgBVCwWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, DATE = _t]),
    Custom1 = Table.ReplaceValue(Source,"A1-","A1",Replacer.ReplaceText,{"ID"}),
    AddRanking = (table, column, newColumn) =>
    Table.AddIndexColumn(Table.Sort(table, {{column, Order.Ascending}}), newColumn, 1, 1),
    #"Grouped Rows" = Table.Group(#"Custom1", {"ID"}, {{"Data", each _, type table}}),
    Transformed = Table.TransformColumns(#"Grouped Rows", {{"Data", each AddRanking(_, "DATE", "Rank")}}),
    #"Expand Data" = Table.ExpandTableColumn(Transformed, "Data", {"DATE", "Rank"}, {"DATE", "Rank"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expand Data", {{"Rank", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expand Data", {{"Rank", type text}}, "en-US")[Rank]), "Rank", "DATE")
 
in
    #"Pivoted Column"

     

2 Replies

  • ChrisMendoza's avatar
    ChrisMendoza
    Resident Rockstar
    3 years ago

    DJT0mss - There's actually a bit that goes into makeing this work so I'll just include the PBIX so you can step through the 'applied steps'.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjTUVdJRMjTSByIjAyMjpVgdoKARFjFDqJghFjFjJDFjqJgBVCwWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, DATE = _t]),
    Custom1 = Table.ReplaceValue(Source,"A1-","A1",Replacer.ReplaceText,{"ID"}),
    AddRanking = (table, column, newColumn) =>
    Table.AddIndexColumn(Table.Sort(table, {{column, Order.Ascending}}), newColumn, 1, 1),
    #"Grouped Rows" = Table.Group(#"Custom1", {"ID"}, {{"Data", each _, type table}}),
    Transformed = Table.TransformColumns(#"Grouped Rows", {{"Data", each AddRanking(_, "DATE", "Rank")}}),
    #"Expand Data" = Table.ExpandTableColumn(Transformed, "Data", {"DATE", "Rank"}, {"DATE", "Rank"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expand Data", {{"Rank", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expand Data", {{"Rank", type text}}, "en-US")[Rank]), "Rank", "DATE")
 
in
    #"Pivoted Column"

     

  • Mikelytics's avatar
    Mikelytics
    Resident Rockstar
    3 years ago

    Hi DJT0mss 

     

    First question for me is why would you do this in Power Query? Because transforming it like this in Powery QUery brings your data into a structure which is bad for analyzing.

     

    Have you already tried working with the matrix visual in the report view and from there to do additional calculations using measures?

    Best regards

    Michael

    -----------------------------------------------------

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    Appreciate your thumbs up!

    @ me in replies or I'll lose your thread.

    -----------------------------------------------------

    LinkedIn