Circular dependency

[VikasD10]

Hello, I want to create Rank in M-query. I have Participant ID and there status completed,Not completed,Deleted and Date Time of that Status. I want to create Rank based on below criteria: If Participant ID found duplicate for Completed I'd rank start with 1 for each Completed ID(New I'd start from 1). Not completed start from A. Deleted start from 1001. I want to create Rank during M-query

[AmiraBedh]

You need to sort your table by the Participant ID and then by the Date Time of the status to make sure that the rank is applied in the correct order.

Then you need to add a custom column that assigns a starting rank based on the status.

You will group your data by Participant ID and then apply the ranking logic within each group. This is where you will also reset the rank for each new 'Completed' ID.

Inside the for exampleGroup Byfor example operation, you'll need to create a custom column that applies the ranking logic based on the category rank you've defined.

For 'Completed', you'll increment numerically.

For 'Not Completed' and 'Deleted', you might need to handle these as special cases, depending on how you want to increment them ('A', 'B', 'C', ... for 'Not Completed' and numeric for 'Deleted').

Since M-query doesn't have a direct function for ranking with such specific conditions, you'll often need to use a combination of for exampleTable.AddIndexColumnfor example (to add a numeric index) and custom logic to reset and apply ranks based on your rule.

let
    Source = TableSource, // put your source table here
    SortedTable = Table.Sort(Source, {{"Participant ID", Order.Ascending}, {"Date Time", Order.Ascending}}),
    AddCategoryRank = Table.AddColumn(SortedTable, "Category Rank", each if [Status] = "Completed" then 1 else if [Status] = "Not Completed" then "A" else 1001),

    Grouped = Table.Group(AddCategoryRank, "Participant ID", {"Data", each _, type table}),

    Ranked = Table.TransformColumns(Grouped, {"Data", each Table.AddIndexColumn(_, "Rank", 1, 1, Int64.Type)})
in
    Ranked

[VikasD10]

Error :A cyclic reference was encountered during evaluation 

[AmiraBedh]

Can you share some data of your original column ?

[VikasD10]