We receive six daily reports from a legacy system. Five of these reports are at the interval level by agent and by queue (contact center data). The Period field in each has a date/time value

I have a workbook whereby I use Power Query (PQ) to retrieve the data from each report, 1-5, and put into a data model. The very first query looks at the first report and generates a set of unique values to be used as a lookup for reports 1-5. The problem comes with the sixth report. The Period in it is only a date value. I attempted to add hours to the date to make it a date/time value (and was successful in adding 12 hours to the date) but the problem is that there may not be corresponding data for that interval for every agent from datasets 1-5. I end up dropping data from report 6.

I believe what I need to do is generate a table  of aggregated data at the agent level (from a pivot table report of datasets 1-5 ?) and then join the data from report 6 to it.

Or, is there a better way?

I've attached a quick diagram of what I am doing so I hope this makes sense.

TcO