Power query date consolidation

[sauravguha]

Hello I have a question if Power Query can help. I have a list of names with different Start and End dates columns with different leave types. Dates may be in continuation but in different rows. i need Power query to club the dates as 1-20 March in one row and 23-26 march in another row as Annual leave and sick leave remains as is in a separate row. Possible?

Name Start Date End Date Leave Type

Mr. Smith 01-Mar-20 05-Mar-20 Annual Leave Mr. Smith 06-Mar-20 09-Mar-20 Annual Leave Mr. Smith 29-Mar-20 31-Mar-20 Sick Leave Mr. Smith 10-Mar-20 20-Mar-20 Annual Leave Mr. Smith 23-Mar-20 26-Mar-20 Annual Leave

result expected:

Mr. Smith 01-Mar-20 20-Mar-20 Annual Leave Mr. Smith 23-Mar-20 26-Mar-20 Annual Leave Mr. Smith 29-Mar-20 31-Mar-20 Sick Leave

The list has many names and may or may not have similar breakdown of leaves. For eg Mr. Dan could have only one Annual leave from 1-30 March... Thank you

[parry2k]

@sauravguha can you post the data in table format and expected output, it is very hard to read the blurb of data. 

[sauravguha]

Sure, here you go...

Name	Property	Date From	Date To	Leave Type
Smith	ABC	2020-04-01	2020-04-05	AL
Smith	ABC	2020-04-06	2020-04-16	AL
Smith	ABC	2020-04-17	2020-04-30	SL
Smith	ABC	2020-03-26	2020-03-28	SL
David	BCD	2020-03-26	2020-03-28	AL
David	BCD	2020-04-28	2020-04-30	AL
David	BCD	2020-03-12	2020-03-12	SL
Monty	BCD	2020-04-17	2020-04-30	AL
Monty	BCD	2020-04-01	2020-04-09	AL
Sid	ABC	2020-03-10	2020-03-10	AL
Sid	ABC	2020-03-11	2020-03-11	AL
Sid	ABC	2020-03-15	2020-03-15	AL
Sid	ABC	2020-03-19	2020-03-24	SL
Sid	ABC	2020-03-25	2020-03-31	SL
Sid	ABC	2020-04-01	2020-04-05	AL

Expected Result:

Name	Property	Date From	Date To	Leave Type
Smith	ABC	26/03/2020	28/03/2020	SL
Smith	ABC	01/04/2020	16/04/2020	AL
Smith	ABC	17/04/2020	30/04/2020	SL
David	BCD	12/03/2020	12/03/2020	SL
David	BCD	01/04/2020	09/04/2020	AL
David	BCD	17/04/2020	30/04/2020	AL
Sid	ABC	10/03/2020	15/03/2020	AL
Sid	ABC	19/03/2020	31/03/2020	SL
Sid	ABC	01/04/2020	05/04/2020	AL

[sauravguha]

Any luck Sir?

[v-easonf-msft]

Hi， @sauravguha 

Here  is demo.

If help ,try follow steps:

1.Add custom as below  and expend the list:

=List.Dates([Date From],Duration.Days([Date To]-[Date From])+1,#duration(1,0,0,0))

2. Remove two column "Date From "  and  "Date to"
3. Then group rows as below

3.Add custom colum "split"as below

=Table.Group(let a = [Data]
in
Table.AddColumn(a, "New"
,each 
  let d = [Date],
  t = Table.AddColumn(
      Table.SelectRows(a, each [Date] <=d),"Temp", each Duration.Days(d-[Date])
      )
  in 
     Table.Min(Table.SelectRows(t, each [Temp] <=Table.RowCount(t)),"Date")[Date]), {"New"}, {{"Date To", each List.Max([Date]), type date}})

4.Then remove the  column "Data " and expand the column "Split"

If I misunderstood your request, please explain more about your calculation logic.

And  I am afraid the excepted result you attached is a bit wrong, as i dont see anywhere Monty's record.

Best Regards,
Community Support Team _ Eason

[sauravguha]

Hello   Thanks for taking time to attend to my query.   I have only one issue. If i have a date range like 26 Mar - 30 Mar 2020 and another one from 1 Apr - 30 Apr 2020, the query is merging it to show 26 Mar - 30 April 2020 when it should remain same since 31st March 2020 is missing in between.   How can we sort this small issue?   Thanks again.   Saurav

[v-easonf-msft]

Hi , @sauravguha 

Not very clear .It is recommended to open another thread to explain your  additional problem in more detail.

Best Regards,
Community Support Team _ Eason

[sauravguha]

Hi, thanks for your assistance and sorry for not being able to clarify as needed. Below is a new example where i believe i would be able to explain more. 

In the first 2 names, Prasanna, since he has two sets of leave dates which are not in continuation, expected result is same as Given data. However, on the name "Said", his first 2 leaves are in continuation, hence, expected result is a date range combining the same and making one and his 3rd date range is kept in a separate row since it is not in continuation with the first two date ranges. 

Was i able to make it clearer? Thank you again for assisting. 

	Given Data						Expected result
Emp ID	Name	Start Date	End Date	Leave Type		Emp ID	Name	Start Date	End Date	Leave Type
E1545	Prasanna	23-Mar-20	30-Mar-20	AL		E1545	Prasanna	23-Mar-20	30-Mar-20	AL
E1545	Prasanna	01-Apr-20	30-Apr-20	AL		E1545	Prasanna	01-Apr-20	30-Apr-20	AL
E116460	Said	10-Mar-20	16-Mar-20	SL		E116460	Said	10-Mar-20	19-Mar-20	SL
E116460	Said	17-Mar-20	19-Mar-20	SL		E116460	Said	28-Apr-20	30-Apr-20	SL
E116460	Said	28-Apr-20	30-Apr-20	SL		E115838	Suraj	31-Mar-20	06-Apr-20	AL
E115838	Suraj	31-Mar-20	06-Apr-20	AL		E115838	Suraj	11-Apr-20	15-Apr-20	AL
E115838	Suraj	11-Apr-20	15-Apr-20	AL		E18433	Manikandan	22-Mar-20	15-Apr-20	AL
E18433	Manikandan	22-Mar-20	28-Mar-20	AL		E116043	George	22-Mar-20	23-Apr-20	SL
E18433	Manikandan	29-Mar-20	15-Apr-20	AL		E115882	Permeshwar	26-Mar-20	30-Mar-20	AL
E116043	George	22-Mar-20	06-Apr-20	SL		E115882	Permeshwar	01-Apr-20	30-Apr-20	AL
E116043	George	07-Apr-20	15-Apr-20	SL		E115587	Illeperumage	18-Mar-20	19-Mar-20	AL
E116043	George	16-Apr-20	23-Apr-20	SL		E115587	Illeperumage	23-Mar-20	26-Mar-20	AL
E115882	Permeshwar	26-Mar-20	30-Mar-20	AL		E115587	Illeperumage	17-Apr-20	30-Apr-20	AL
E115882	Permeshwar	01-Apr-20	30-Apr-20	AL		E117332	Hosny	01-Mar-20	11-Mar-20	SL
E115587	Illeperumage	18-Mar-20	19-Mar-20	AL		E117332	Hosny	26-Mar-20	30-Apr-20	AL
E115587	Illeperumage	23-Mar-20	26-Mar-20	AL
E115587	Illeperumage	17-Apr-20	30-Apr-20	AL
E117332	Hosny	01-Mar-20	01-Mar-20	SL
E117332	Hosny	02-Mar-20	02-Mar-20	SL
E117332	Hosny	03-Mar-20	03-Mar-20	SL
E117332	Hosny	04-Mar-20	04-Mar-20	SL
E117332	Hosny	05-Mar-20	05-Mar-20	SL
E117332	Hosny	06-Mar-20	06-Mar-20	SL
E117332	Hosny	07-Mar-20	07-Mar-20	SL
E117332	Hosny	08-Mar-20	08-Mar-20	SL
E117332	Hosny	09-Mar-20	09-Mar-20	SL
E117332	Hosny	10-Mar-20	10-Mar-20	SL
E117332	Hosny	11-Mar-20	11-Mar-20	SL
E117332	Hosny	26-Mar-20	16-Apr-20	AL
E117332	Hosny	17-Apr-20	24-Apr-20	AL
E117332	Hosny	25-Apr-20	30-Apr-20	AL

[amitchandak · Accepted solution]

@sauravguha , shared solution in PM. Created start date and end dates based on the continuous streak

[sauravguha]

Thank you so much for the assistance, it works very well. will try on a live data and let you know if i face any challenge.