Pivot table by employeeID/Consecutive days

Power Query Data Setup
1

Hi,

I am trying to transform an employee absent table that is populated on a day by day basis to group by employee id to show the from and to date and number of days absent. Sounds quite simple, but the caveat is that within the employee id group, the from and to dates should also be grouped by consecutive days absent.

I have attached an Excel sheet that shows an example of what the data could look like in its raw state and what the desired table should look like.

I have also attached a PBI, I’m not sure if I am on the right track, but I am now stuck on how to identify consecutive dates per employee and how to create a from date, to date and number of days absent columns.

Any help will be much appreciated.
Thanks,
Pete.
Employees.xlsx (17.3 KB)
Leave dates.pbix (53.0 KB)

2

There must be an easier way, but see if it helps.
Leave dates.pbix (61.5 KB)

Sem título
Sem título1191×321 23.1 KB

1 Like
3

Hi Vilmar,

Thank you so much for this solution. It works like a dream and resolved my problem.

Very much appreciated :smile:

Cheers,
Pete.