Pivot rows to column

[DJT0mss]

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

[Mikelytics]

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

[ChrisMendoza · Accepted solution]

@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"