Forum Discussion
Anonymous
6 years agoNot applicable
Ranking Dates in Power Query
I would like to rank the dates that are coming across to me in Power Query with 1 being the most recent. The data would come across something similar to this: Date Value 31-Dec-19 369 ...
- 6 years ago
OK, here it is in Power Query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc6xCoAwDATQf8ncQpMm0ezi4C+UDipd/f/R0goVXR933KUEEf1STo8GDqIaZJcA/bZfnkKlGakTDTOVZjwoGn+JBRtRGDZJ7KavqvIzupajEwX5xTDUI/kG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Descending}}), #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Date"}, {{"Values", each _, type table [Date=date, Value=number]}}), #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1), #"Expanded Values" = Table.ExpandTableColumn(#"Added Index", "Values", {"Value"}, {"Values.Value"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded Values",{{"Values.Value", "Value"}, {"Index", "PQ Rank"}}) in #"Renamed Columns"Updated PBIX attached. From knowledge gained here: https://www.excelguru.ca/blog/2018/06/14/ranking-method-choices-in-power-query/
Greg_Deckler
6 years agoCommunity Champion
OK, here it is in Power Query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc6xCoAwDATQf8ncQpMm0ezi4C+UDipd/f/R0goVXR933KUEEf1STo8GDqIaZJcA/bZfnkKlGakTDTOVZjwoGn+JBRtRGDZJ7KavqvIzupajEwX5xTDUI/kG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Descending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Date"}, {{"Values", each _, type table [Date=date, Value=number]}}),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
#"Expanded Values" = Table.ExpandTableColumn(#"Added Index", "Values", {"Value"}, {"Values.Value"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Values",{{"Values.Value", "Value"}, {"Index", "PQ Rank"}})
in
#"Renamed Columns"
Updated PBIX attached. From knowledge gained here: https://www.excelguru.ca/blog/2018/06/14/ranking-method-choices-in-power-query/