Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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"
Proud to be a Super User!
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.
-----------------------------------------------------
@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"
Proud to be a Super User!
User | Count |
---|---|
93 | |
83 | |
77 | |
71 | |
65 |
User | Count |
---|---|
114 | |
101 | |
96 | |
65 | |
60 |