difference between rows within a category

[Anonymous]

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	1	Asia	1	11/4/2019	0
China	1	Asia	2	12/4/2019	1
China	1	Asia	3	10/24/2020	2
Spain	2	Asi	1	12/24/2020	3

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?

[v-juanli-msft]

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

[edhans]

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]

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

[Greg_Deckler]

@ImkeF