Hello Phil! I just hope that I understood your use case and requirements right because configuring this automation took a while.
So, I reproduced the provided data structure in a SharePoint list. The 'Status' and 'Work prgoramme' are choice columns because I assumed that you have restricted and pre-defined options there.
Then, I created a Plumsail process for a PowerPoint template. This format has a restriction in processing images: you need to put a picture-placeholder and define a token in its alternative text. Thus, you need to know beforehand what number of pictures you will have in the presentations. In your case it is not possible so I just drop this part and add only names and job titles to a separate slide to overview the team for a current programme. If you use a Word template, the pictures can be added dynamically too. The PPTX template is attached.
template.pptx (152.8 KB)
Then I created a Power Automate flow started manually. First, I initialised four array variables, three of them are empty and one contains the possible options in the 'Work programme' field.
The next step is to iterate over each item in the 'programmes' array and retrieve for each one the related items in the data source list. For that, I used oData filtering.
I iterated over each item for the current programme to retrieve photo of the responsible person and build an object for each team member.
If the current object was not appended to the 'responsibles' array (it does not contain the object), I append it.
After that I iterated over each item for the current programme again. It is not necessary, such structure I got after testing different parts of the flow. You can add the steps below to the same 'Apply to each' control that was used for building the 'responsibles' array. So, first, I added a new property 'currentMonth' to the current item retrieved from the list using this Power Automate expression:
addProperty(item(), 'currentMonth', items('Apply_to_each_(items)')?[formatDateTime(utcNow(), 'MMM', 'en-US')])
item() refers to the current item in the 'Apply to each' control. 'currentMonth' is the name of the property added. It should match to the last part of the accordant token in the template table. Now the most complex part. All month columns in the data source list should be named in the US format using three letters. You can follow another pattern but my example is for that case. Expression formatDateTime(utcNow(), 'MMM', 'en-US') returns the name of the current month in the described format. Now, it returns 'Jul'. Thus, expression items('Apply_to_each_(items)')?[formatDateTime(utcNow(), 'MMM', 'en-US')] refers to the value in the 'Jul' column for the current item in the 'Apply to each' control. The entire expression addProperty(...) adds property named 'currentMonth' and assigns to it the value contained in the 'Jul' column. In the next month, it will take the value from the 'Aug' column if you have such.
Then I added property 'previousMonth' using the same logic. But instead of the utcNow() returning the current timestamp, I used getPastTime(). Also, I added the property to the outputs of the previous step.
addProperty(outputs('Compose_(current_month)'), 'previousMonth', items('Apply_to_each_(items)')?[formatDateTime(getPastTime(1, 'Month'), 'MMM', 'en-US')])
The result is appended to the 'items' array.
Once the arrays containing team members and table items are ready, I compose an object for the current programme and append it to the 'data' array. There, I include the mentioned arrays and the name of the programme.
Before moving to the next programme, it is necessary to empty the 'items' and 'responsibles' arrays.
Finally, I start the Plumsail process.
The result file is attached.
test-pptx(1).pptx (169.4 KB)
