Forum Discussion
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 |
| 1-Jan-20 | 812 |
| 12-Jan-20 | 965 |
| 4-Jan-20 | 394 |
| 4-Jan-20 | 451 |
| 20-Jan-20 | 753 |
| 26-Jan-20 | 364 |
| 1-Feb-20 | 205 |
| 26-Jan-20 | 102 |
And I would like for it to be set like this
Date | Value | Rank |
| 31-Dec-19 | 369 | 7 |
| 1-Jan-20 | 812 | 6 |
| 12-Jan-20 | 965 | 4 |
| 4-Jan-20 | 394 | 5 |
| 4-Jan-20 | 451 | 5 |
| 20-Jan-20 | 753 | 3 |
| 26-Jan-20 | 364 | 2 |
| 1-Feb-20 | 205 | 1 |
| 26-Jan-20 | 102 | 2 |
Is there an MDX function or method to rank these easily? I hav also seen different examples in DAX, which I could do but i would be interedted in learning how to do this ranking in MDX/Power query
Thanks!
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/
5 Replies
Whoops, didn't read all the way through and did it in DAX. Since I already did it, I'll post it. ImkeF might have a way of ranking in Power Query.
Rank = RANKX(ALL('Table'),'Table'[Date],,DESC,Dense)PBIX is attached.
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/
Why don't you just sort Descending by date, then add an Index column that starts at 1?
If for some odd reason you wanted to preserver the order (which won't matter to DAX once it is imported) create a starting Index, then sort by date, then create a Ranking index, then resort by the original index. But I think that is unnecessary
Almost edhans , see my post that provides the correct Power Query. You sort by Date, Group by Date and use All Rows, Add Index starting at 1 and then expand your column with the table in it. Bit of renaming and all set to go.
Hi Anonymous
did Greg_Deckler s solution work for you? If so, please mark it as answer. If not, please explain what's still missing.
It is written in the M-language that is used in the query editor (the "Power Query"-part of Power BI).
MDX cannot be used in there (by the user).
