ranking dates in Power Query

[AndrewPF]

If I have the following in a table:

date
01/09/21
01/09/21
01/10/21
01/10/21
01/10/21
01/11/21
01/11/21
01/11/21
01/11/21
01/12/21
01/12/21
01/12/21
01/01/22
01/01/22

and I need to get to the following:

date             index
01/09/21         1
01/09/21         1
01/10/21         2
01/10/21         2
01/10/21         2
01/11/21         3
01/11/21         3
01/11/21         3
01/11/21         3
01/12/21         4
01/12/21         4
01/12/21         4
01/01/22         5
01/01/22         5

can I do it in Power Query?

The number of rows for each date is not fixed, and I have other columns in my table which I need to preserve.

[rezadp]

You could always add a table in Power Query with a distinct list of dates and then add the index.  Merge your table with the distinct list and return the index column...at least you know the data is persisted and you won't experience weird behaviour with calculated columns and filter context.

[v-luwang-msft · Accepted solution]

Hi @AndrewPF ,

Pls refer the below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUN7DUNzJUitXB5BgaEMcxJINjpK+AwsPBASIjIzROLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"Count", each _, type table [Date=nullable date]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Expanded Count" = Table.ExpandTableColumn(#"Added Index", "Count", {"Date"}, {"Count.Date"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Count",{"Count.Date"})
in
    #"Removed Columns"

Before:

After:

You could download my pbix file if you need.

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[HotChilli]

'Group By' using date with 'all rows' as aggregation.

Add Index column.

Expand All column to bring back fields

[johnt75]

Not sure if you can do that in Power Query but in DAX you could use RANKX, e.g.

Ranking = RANKX( 'Table', 'Table'[Date],, ASC, Dense)

[AndrewPF]

Hi JohnT, 

I tried it but I keep getting the messge: "a single value for column 'Date' in table '[table name]' cannot be determined.  

Any idea? 

[johnt75]

It needs to be a column not a measure