Ranking Dates in Power Query

[Anonymous]

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!

[edhans]

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

[Greg_Deckler]

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. 

[ImkeF]

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).

[Greg_Deckler · Accepted solution]

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]

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.