How to assign chronological order to dates by created date for Project in Power Query - No expansion

[jmdaily83]

Hello,

 I have a report in which the client created it in Excel and I'm running into trouble trying to replicate a lot of the stuff they did in PowerBI since you cannot make selection by cells. As a work around for one of the items I'm attempting to assign a chronological order based on the created date (newest to oldest) for items that have been submitted for different projects in the same column.  I have tried this with some help from other users in the community but my issue is that it is expanding the rows and making my 500 row table over 80,000 rows, which is not what I want. 

Below is a sample of the data that I have 

Project	Date Created
A	4/25/2023
A	4/19/2023
B	11/20/2023
B	11/15/2023
B	6/1/2023
B	5/31/2023
B	5/1/2023
B	4/30/2023
C	11/27/2023
C	5/5/2023
C	2/7/2023
C	1/12/2023
C	12/16/2022
C	12/16/2022
C	12/8/2022
D	8/25/2023
D	5/19/2023
E	9/22/2023
E	9/19/2023
E	9/13/2023
F	6/26/2023
F	6/23/2023
G	5/15/2023

and the Order column below is what I would like to add to show the results as below:

Project	Date Created	Order
A	4/25/2023	1
A	4/19/2023	2
B	11/20/2023	1
B	11/15/2023	2
B	6/1/2023	3
B	5/31/2023	4
B	5/1/2023	5
B	4/30/2023	6
C	11/27/2023	1
C	5/5/2023	2
C	2/7/2023	3
C	1/12/2023	4
C	12/16/2022	5
C	12/16/2022	6
C	12/8/2022	7
D	8/25/2023	1
D	5/19/2023	2
E	9/22/2023	1
E	9/19/2023	2
E	9/13/2023	3
F	6/26/2023	1
F	6/23/2023	2
G	5/15/2023	1

[ronrsnfld]

In Power Query, it is fairly efficient to GroupBy Project, then add an Index Column within the aggregation.

It seems your original data is appropriately sorted. If that is not the case, then you will need to add a sorting step.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdBLDoAwCATQu3Rtgkyl6tL/IYz3v4b4S0u7cMkLYcjsuxtc5RqCEGp4d1SfcB9lVGHWuSAWS4HYgpAvJIOGfHJ4erNaS0JiAZRt6DPIBMThIvxQF2VW6Uwd8/NzUseioiNyKXfYR1nvehAKSXa2J+tLP04=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Date Created" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Date Created", type date}}),

//if data not sorted, will need to add a sorting step by Project and Date Created

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Project"}, {
        {"all", each Table.AddIndexColumn(_,"Order",1,1,Int64.Type),

            type table [Project=nullable text, Date Created=nullable date, Order=Int64.Type]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Date Created", "Order"}, {"Date Created", "Order"})
in
    #"Expanded all"

Results

[jmdaily83]

Thank you, this method worked as well!

[Greg_Deckler]

@jmdaily83 Not sure how to do that exactly in Power Query but perhaps you can use a DAX solution or adapt the DAX solution to M code:

Cthulhu - Microsoft Fabric Community

[Greg_Deckler · Accepted solution]

@jmdaily83 OK, how about this to add a custom column?

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let 
__Project = [Project],
__Date = [Date Created],
__Table = Table.SelectRows( #"Changed Type", each ([Project] = __Project and [Date Created] > __Date ) )
in
  Table.RowCount(__Table) + 1)

[jmdaily83]

This worked perfectly, thank you so much!