Forum Discussion
Ranking Dates in Power Query
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).
