Receiving error when attempting to provide a chronological order number off of a date column - power

[jmdaily83]

Hello again,

  I'm attempting to create a chronological order based off a date column for specific projects

Project   Date         Order

A            1/1/24       1

B             2/1/24       1 

B           12/1/23       2

C           12/1/23       1

C           10/1/23       2

C            8/1/23        3

I am using this power query formula but am receiving an error for an unknown reason. I have confirmed there are no errors in any of the columns I'm using and they are formatted properly as well. 

Table.AddColumn(
#"Removed Errors",
"ChronologicalOrder",
each Text.From(
List.PositionOf(
List.Sort(
Table.SelectRows(
#"Removed Errors",
[Opp Name] = [Opp Name]
)[Created],
Order.Descending
),
[Created]
) + 1
))

Error:

Expression.Error: We cannot convert the value true to type Function.
Details:
Value=TRUE
Type=[Type]

[jmdaily83]

@v-heq-msft @rajendraongole1  Thank you both for your help. 

It appears to be working in both but it is expanding the table from about 500 rows to over 80,000 rows when I expand the new column.  is there a way to avoid this?  

[jmdaily83]

@rajendraongole1 @v-heq-msft  would either of you be able to help with the issue of it expanding into thousands of rows?

[v-heq-msft]

Hi  @jmdaily83 ,
I’d like to acknowledge the valuable input provided by the @rajendraongole1   . Here is what I want to add:
You can try this code

Table.AddColumn(
    #"Removed Errors",
    "ChronologicalOrder",
    each Text.From(
        List.PositionOf(
            List.Sort(
                Table.SelectRows(
                    #"Removed Errors",
                    (row) => row[Project] = [_][Project]
                )[Date],
                Order.Descending
            ),
            [_][Date]
        ) + 1
    )
)

Best regards,
Albert He
 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[rajendraongole1]

Hi @jmdaily83 - The condition [Opp Name] = [Opp Name] does not correctly filter the rows. We need to use a proper function to filter the rows within Table.SelectRows.

FilteredRows = Table.SelectRows(RemovedErrors, each [Opp Name] = OppName)

This filters the rows where Opp Name matches the current row's Opp Name.

further sort dates with List.sort function from M with above. Check it and let know.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!! 

[jmdaily83]

@rajendraongole1   This seems to work but expands the table into thousands of rows which I do not need.  Is there any way to do this w/out expanding the rows?