Forum Discussion
How to match activity times against a schedule in Power Query
Hello,
I have two tables I am working with.
One is a daily 'schedule' of times when certain activities should occur and includes the activity name, start time, and end time. In this table, activities are repeated with multiple start and end times, and start and end times can be re-used across activities. This table represents all possible times that activities can occur.
| Activity | Start | End |
| A | 12:00:00 AM | 1:00:00 AM |
| A | 2:30:00 AM | 3:30:00 AM |
| A | 6:00:00 PM | 7:00:00 PM |
| B | 12:00:00 AM | 1:00:00 AM |
| B | 6:00:00 PM | 7:00:00 PM |
| C | 2:30:00 AM | 3:30:00 AM |
| C | 6:00:00 PM | 7:00:00 PM |
The second table is a list of activities along with date and what time the activity started. Activities are started multiple times per day:
| Activity | Date | Start Time |
| B | 01-01-23 | 12:27:00 AM |
| C | 01-01-23 | 3:44:00 AM |
| A | 01-01-23 | 12:56:00 AM |
| B | 01-02-23 | 6:03:00 PM |
| C | 01-02-23 | 6:15:00 PM |
| A | 01-03-23 | 3:15:00 AM |
Ultimately, I'd like to create a new table that summarizes the information as follows:
| Date | Activity | Total Count | Count (On Time) | Count (Early) | Count (Late) |
| 1-1-23 | A | ||||
| 1-1-23 | B | ||||
| 1-1-23 | C | ||||
| 1-2-23 | A | ||||
| 1-2-23 | B | ||||
| 1-2-23 | C | ||||
| 1-3-23 | A | ||||
| 1-3-23 | B | ||||
| 1-3-23 | C |
The Count (On Time) would represent the daily count of activities that started between the start and end time, the Count (Early) would represent the daily count of activities that started up to 30 minutes prior to the start time, and the Count (Late) would represent the daily count of activities that started up to 30 minutes after the end time.
I can easily create the new table with Date, Activity, and Total Count using SUMMARIZE(). I am struggling to create new columns for Count (On Time), Count (Early), and Count(Late).
Any help with the appropriate DAX to get these figured would be greatly appreciated!
Thanks in advance.
Matt
Hi sellersmd ,
I think the first table is the schedule table, and the second table is the table of the actual start time, right?
Here's my solultion. It's recommended to complete it in Power Query.
1.You can merge the Actual table with the Schedule table. Reference: Merge Queries
2.Expand the tables in the Schedule column.
3.Create a custom column to get the On Time, Early and Late. Refernce: Add a custom column - Power Query | Microsoft Learn
4.Remove the rows contains null.
Click OK.
5.Remove the unneeded columns and add a custom column with 1.
6.Pivot the Custom column to get the countings.
Pivot columns (Power Query) - Microsoft Support
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
4 Replies
Hi sellersmd ,
I think the first table is the schedule table, and the second table is the table of the actual start time, right?
Here's my solultion. It's recommended to complete it in Power Query.
1.You can merge the Actual table with the Schedule table. Reference: Merge Queries
2.Expand the tables in the Schedule column.
3.Create a custom column to get the On Time, Early and Late. Refernce: Add a custom column - Power Query | Microsoft Learn
4.Remove the rows contains null.
Click OK.
5.Remove the unneeded columns and add a custom column with 1.
6.Pivot the Custom column to get the countings.
Pivot columns (Power Query) - Microsoft Support
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi Stephen,
Elegant solution and easy to follow. I was unaware that when you merged queries you could do a many-to-one match - that was exactly what I needed! Thanks for your help.
KeyurPatel14Responsive Resident
Hii sellersmd ,
Can you please provide full output so that I can help you quicker??Hello KeyurPatel14!
Thank you for taking the time to reply. Here are dropbox links to a test schedule and test activity data. I hope thats what you were looking for by full output. If not, please clarify and I will provide whatever is needed!
Thank you in advance!
