Results 1 to 7 of 7

Thread: Incrementing index on multiple categories - Power Query M

  1. #1
    Neophyte olabodeoa's Avatar
    Join Date
    Feb 2021
    Posts
    4
    Articles
    0
    Excel Version
    2013

    Incrementing index on multiple categories - Power Query M



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    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

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,661
    Articles
    0
    Excel Version
    Office 365 Subscription
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Neophyte olabodeoa's Avatar
    Join Date
    Feb 2021
    Posts
    4
    Articles
    0
    Excel Version
    2013
    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

  4. #4
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    120
    Articles
    0
    Excel Version
    Office 365
    Is it possible that your examples for ID F are not correct?

  5. #5
    Neophyte olabodeoa's Avatar
    Join Date
    Feb 2021
    Posts
    4
    Articles
    0
    Excel Version
    2013
    ID F is correct, the status resets to 1 when completed

  6. #6
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    120
    Articles
    0
    Excel Version
    Office 365
    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 !?!

  7. #7
    Neophyte olabodeoa's Avatar
    Join Date
    Feb 2021
    Posts
    4
    Articles
    0
    Excel Version
    2013
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •