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