Forum Discussion
Solved
Power query date consolidation
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
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
sauravguha , shared solution in PM. Created start date and end dates based on the continuous streak
9 Replies
sauravguha can you post the data in table format and expected output, it is very hard to read the blurb of data.
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 - Any luck Sir?
