Power Platform Community Forum Thread Details

I have a file with remit invoice data for multiple agencies. I need to filter by agency and create a file for each agency with just that agency data within the file. The title of the file should be the agency name. Separately, I have a list of emails with the appropriate agency name next to each email. Some of the agencies have multiple emails and there is a single email that should be CC'd on each email. The agencies can (and most likely will) change each time it is run. Is there a way to automate this process?

I am having trouble getting the files to create. I have used Macros to split files before but the entire look has changed and I am a bit confused. I have also used Mail Merge in the past to do a similar project with sending the attachments but I am wondering if using the Power Automate can make the process a bit easier.

I do have these in tables and the file is a macro.

Initial File

Agency Name	Invoice Number	Staff Name	Shift Date	Inv. Amount	Fee	Agency Amount
Agency 1	1001	John Doe	1/1/2023	1,500	100	1,400
Agency 1	1001	Jane Doe	1/1/2023	1,000	100	900
Agency 2	2001	Grace Doe	1/1/2023	2,000	100	1,900
Agency 2	2001	Frank Doe	1/1/2023	1,300	100	1,200
Agency 3	3001	Sally Doe	1/1/2023	1,200	100	1,100
Agency 3	3001	Frank Doe	1/2/2023	1,300	100	1,200
Agency 3	3001	Sally Doe	1/2/2023	1,200	100	1,100

1st File

File Name: Agency 1

Agency Name	Invoice Number	Staff Name	Shift Date	Inv. Amount	Fee	Agency Amount
Agency 1	1001	John Doe	1/1/2023	1,500	100	1,400
Agency 1	1001	Jane Doe	1/1/2023	1,000	100	900

2nd File

File Name: Agency 2

Agency Name	Invoice Number	Staff Name	Shift Date	Inv. Amount	Fee	Agency Amount
Agency 2	2001	Grace Doe	1/1/2023	2,000	100	1,900
Agency 2	2001	Frank Doe	1/1/2023	1,300	100	1,200

3rd File

File Name: Agency 3

Agency Name	Invoice Number	Staff Name	Shift Date	Inv. Amount	Fee	Agency Amount
Agency 3	3001	Sally Doe	1/1/2023	1,200	100	1,100
Agency 3	3001	Frank Doe	1/2/2023	1,300	100	1,200
Agency 3	3001	Sally Doe	1/2/2023	1,200	100	1,100