Incrementing index on multiple categories - Power Query M

O

olabodeoa

New member
Joined
Feb 10, 2021
Messages
4
Reaction score
0
Points
0
Excel Version(s)
2013
Hi all, I have two fields in my data and want to be able to assign incremental values when the status for each ID is "Completed". Please see below
[FONT=&quot]Problem
ID Status
A In Progress
A In Progress
A In Progress
A Completed
A In Progress
A In Progress
B Completed
C In Progress
D In Progress
E In Progress
F Completed
F Completed
F In Progress
What I Require is
ID Status No
A In Progress 1
A In Progress 2
A In Progress 3
A Completed 4
A In Progress 1
A In Progress 2
B Completed 1
C In Progress 1
D In Progress 1
E In Progress 1
F In Progress 1
F Completed 2
F In Progress 1
Please can you help? Thank you all
[/FONT]
 
Hi AliGW , thanks for your help on this, I think I missed out a key information in my original post. In your case sub area is different for every area. In my case the Status cant be sorted as it changes over time. I have added the date column to my initial table to explain it clearer. I appreciate your help

Column 1 - ID, Column 2 - Status - Column 3 - Date
Problem
ID Status Date
A In Progress 01/01/2021
A In Progress 02/01/2021
A In Progress 03/01/2021
A Completed 04/01/2021
A In Progress 05/01/2021
A In Progress 06/01/2021
B Completed 07/01/2021
C In Progress 08/01/2021
D In Progress 09/01/2021
E In Progress 10/01/2021
F Completed 11/01/2021
F Completed 12/01/2021
F In Progress 13/01/2021


What is required
ID Status Date Required
A In Progress 01/01/2021 1
A In Progress 02/01/2021 2
A In Progress 03/01/2021 3
A Completed 04/01/2021 4
A In Progress 05/01/2021 1
A In Progress 06/01/2021 2
B Completed 07/01/2021 1
C In Progress 08/01/2021 1
D In Progress 09/01/2021 1
E In Progress 10/01/2021 1
F Completed 11/01/2021 1
F Completed 12/01/2021 1
F In Progress 13/01/2021 2
 
Is it possible that your examples for ID F are not correct?
 
ID F is correct, the status resets to 1 when completed
 
Open your eyes and compare the 3 F-lines in the "Problem" section with the 3 F-lines in the "What I Require is" section of the First Post. Then you will see that the statuses are different.

Furthermore the change from 4 to in at ID A in the 1st post is comprehensible, because after "completed" the count should start again with 1.

But why now in post #3 at F the last entry should be displayed with 2 instead of 1, I can't understand, because the previous line shows the status "completed" and therefore the next line should start with 1 again !?!
 
Hi Acolyte,

Thanks once again, for ID F thats how my data is. An ID can go back to in progress status after completed. So in my case, the documents get reviewed many times even after it has been completed previously. Nothing has changed with the query. I appreciate any help
 
You must log in or register to reply here.
Back
Top