Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

difference between rows within a category

Hello, I am using power querry for data processing and I need help getting the difference between rows. 

I have a table that has the following rows and columns:

Country     Code    Region     Stage                           Start_Date                   Index 

China 1Asia111/4/2019 0
China 1Asia212/4/20191
China1Asia310/24/2020 2
Spain2Asi112/24/20203

 

 

I want to add an end date column for each phase, and for phase 3 i want it to have a date of 12/31/2025. I want to eventually join this to a table of all of the months so that I can count how many employees i need each month. 

Can you help me do this using power query within excel?

Need Help

4 Replies

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    Hi Anonymous 

     

    Can you be a bit more specific? For example:

    1. You want Phase 3 to have an end date of 12/25/2025. What is a "phase?" You have no "Phase" columns.
    2. Where are you getting 12/25/2025? How is that being calculated?

    I attaching more data, please see the guildlines for sample data below. The table you provided was not easy to get into a file.

    How to Get Your Question Answered Quickly
    How to provide sample data in the Power BI Forum

     

    Greg_Deckler - I'm not Imke (not even close!), but there are a lot of us here to help on Power Query too. ğŸ˜Š

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      6 years ago
      Understood edhans. I'll have to add you to my macro invoking ImkeF ! ImkeF and I go way way back, I've been giving shout outs to her on Power Query questions for years! 🙂
  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    Please follow Greg_Deckler and edhans 's suggestions, provide more details.

    As i assumed, if "Stage" from your table refers to "phase" in your statement,

    and your requirement is like stage1,2,3 have different end dates,

    then you could add conditional column like the following:

    As for another requirement: "count how many employees i need each month", 

    Do you mean to count the employees who are between start date and end data?

     

    Best Regards

    Maggie

    sss