Forum Discussion
COUNTIFS in Power Query (M)
OK, so I started from scratch just to make sure I was working through the issue correctly. I've attached the working PBIX at the bottom so I won't post each individual bit of code here.
- Your Days table I didn't do anything with directly, but is used later in a crossjoin.
- Your Events table I did some pre-prep on to get it looking like this:
- Your Employees table I used as the base for the final process, crossjoining the Dates table and merging the prepped Events table, before double-grouping to generate the daily stats.
Here's the output:
I think you'd got most of the way here already, so it's probably just the final groupings in the Employees table that are relevant. The first grouping just gets you to a single record per employee/date so you're not double-counting. The second grouping uses custom List.Counts to pick out the stats you're after.
Pete
Sorry, I did that in a rush.
1. All dates from the Days table are developed in the Employees table (with a simple Key = 1)
2. Because there is a start and end date, all the events from the table Events are developed (eg. if there is RTJ from 26/12 to 29/12, 4 rows are created). These events mean that employees are away from office
3. If there is M (morning) or A (afternoon) in the Type column of the event, it means it is valued as half a day of leave, so it's valued as 0.5
4. These days are merged with the days of each employee, in order to know if they are here or not on the aforementioned days. If there is an event without type then 1, with a type then 0.5, no event then 0
And yes, your endpoint is right. Thanks!!
OK, so I started from scratch just to make sure I was working through the issue correctly. I've attached the working PBIX at the bottom so I won't post each individual bit of code here.
- Your Days table I didn't do anything with directly, but is used later in a crossjoin.
- Your Events table I did some pre-prep on to get it looking like this:
- Your Employees table I used as the base for the final process, crossjoining the Dates table and merging the prepped Events table, before double-grouping to generate the daily stats.
Here's the output:
I think you'd got most of the way here already, so it's probably just the final groupings in the Employees table that are relevant. The first grouping just gets you to a single record per employee/date so you're not double-counting. The second grouping uses custom List.Counts to pick out the stats you're after.
Pete