Forum Discussion
How to match activity times against a schedule in Power Query
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 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.
