flow – analyse calendar with Power Automate

For every day I want to analyse the entries of a calendar.

To pick all appointments from a calendar is the easy part. I just do a getItems on my calendar with an appropriate OData-Filtering.

Now having all the dates in the returned JSON I want to sum up simultaneous dates for every hour of the day and for each area occupied.

i.e. if let’s say there are three meetings in area A, one from 10:00 – 13:00, one from 12:00 – 16:00 and one from 14:00 to 16:00, I want to derive an array for area A with the first 10 elements being zero, the next two being 1, then one element with 2, then one with 1, two with 2 and the rest with 0. Afterwards I want to write that outcome into a different calendar for visualisation.

Simplest method is to take each meeting and add 1 to every hourly element in between start and end. And do that for every area. This needs 3 nested loops, the first one iterating over the areas, the second one iterating over the meetings there and the last one iterating over all the hours the meetings span.

However, consider (in this case) 15 or more meetings a day with a typical duration of 6 hours this alone will produce 70 iterations. As I plan to implement a regular job checking the next 60 days in the calendar, the number of actions will be enourmous. I think this flow migtht take hours to finish.

Is there some way I can do that with the more elaborate tools in power automate? E.g. by some nifty array operations?

Or did I even miss some tools I have within SP? E.g. some good filters or views to prepare the data in a useful way?