Power Automate flow to update a new files multiple choice column in SharePoint from matching previous files

Question

I am trying to create an email archiving solution. I currently have a flow that will trigger when a new email arrives into an inbox, creating the email as a file within a SharePoint folder. To avoid duplications I use a time stamp added to the subject of the email for the file name ("Title" column in SharePoint). The email address of the sender is added to the column "Enquirer". There is another multiple choice column called "Owner" that will allow for the individual responsible for the chain of emails to be meta-tagged. This all works fine.

I am also trying to create a second flow that will trigger when a file is created (from the flow above) to search the SharePoint folder and should another file/s equal the same enquirer and contain the file name (removing the date stamp at the end) it will use the "Owner" multiple choice column in my SharePoint folder (therefore the values are stored as an array) from the matching files to update the "Owner" value in the newly created file.

For example an email may come in called "Policy help CUI support". The first flow creates the files "Policy help CUI support - 24-10-2023 16_15_02.eml". The Enquirer column is populated with the email address "[email protected]". The second flow then needs to scan the SharePoint folder for any file containing "Policy help CUI support" and the same enquirer "[email protected]". The flow will find one or many of the same file I need the Owner of these saved emails to be automatically updated to the new file. There may be 3 matching files, with Owners: File 1 - "Joe Bloggs", "Mary Smith", "Mark Jones". File 2 - "Joe Bloggs", "Mary Smith". File 3 - "Joe Bloggs", "Sarah Jane". So the new file needs the Owner column updated to "Joe Bloggs", "Mary Smith", "Mark Jones", "Sarah Jane".

I have managed to filter down to find the matching files and I have been able to update the Owner to 1 entry, but because there are multiple files and the Owner is an array the flow automatically puts the actions in for each loops, I am struggling to extract the values, append them and remove the duplicates. Happy to add screenshots of my current flow to help. I have tried all sorts but nothing seems to work. The closest I got had every single choice updated without the duplicates removed. So the above example would have updated the Owner to - "Joe Bloggs", "Mary Smith", "Mark Jones", "Joe Bloggs", "Mary Smith", "Joe Bloggs", "Sarah Jane".

Answer 1

You can remove duplicates in an array in Power Automate by doing a union on itself. Eg union(outputs('someArray'), outputs('someArray'))