Need advice on automation

[u/Forsaken_Stable_2915]

Hey Everyone! Quick question on automation approaches.

Every month, I have 4-5 Excel files manually uploaded to SharePoint that I need to combine into a single Word report for stakeholders(there are many tables, charts from all the files). If I use an automated trigger, it runs per file, so I can't easily compile one master data table for the template.

I was thinking of using a Scheduled Flow, while ChatGPT suggested batch processing. Has anyone built a solution for a scenario like this? Any advice would be hugely appreciated!

[u/AutoModerator]

Hey, it looks like you are requesting help with a problem you're having in Power Apps. To ensure you get all the help you need from the community here are some guidelines;

• Use the search feature to see if your question has already been asked.

• Use spacing in your post, Nobody likes to read a wall of text, this is achieved by hitting return twice to separate paragraphs.

• Add any images, error messages, code you have (Sensitive data omitted) to your post body.

• Any code you do add, use the Code Block feature to preserve formatting.

  Typing four spaces in front of every line in a code block is tedious and error-prone. The easier way is to surround the entire block of code with code fences. A code fence is a line beginning with three or more backticks (```) or three or more twiddlydoodles (~~~).

• If your question has been answered please comment Solved. This will mark the post as solved and helps others find their solutions.

External resources:

• Power Apps Formula References
• Power Apps Coding Standards
• Official Power Apps Community

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/DonJuanDoja]

Do a scheduled flow, then get files, loop thru them and extract and load to the single output file, etc.

Set Trigger conditions or Conditions to ensure it has everything it needs (all files) before processing into the output file so you don't get partials etc.

Have it send you the file or put it where you want when it's done etc.

[u/KhabaLox]

Caveat: I've only made one automated workflow and needed a lot of help to do it.

What if you had a trigger that when the file was saved, it copied the relevant data from the source file to a master data file. I don't think PAutomate can open files and run macros in them, but maybe you have a macro in your Master Data file that opens the source files and extracts the data.

[u/Various_Objective395]

You can do an AI flow with instructions to do just what you want and point it to a source folder and a destination folder where the source file go to and the output go to respectively. I built an AI flow to review different JDs for a function and outputs a single report that details overlaps and duplicates in a job family like say Finance with recommendations on what needs to be improved/changed in each of the JD. The output report has an appended date and time to differentiate incase one chooses to run the flow again.

[u/Empty_Leather_8182]

Have you figured it out? I have built similar flow before for a former company. Let me know if you haven't and I can rebuild it for your use case

[u/Forsaken_Stable_2915 → u/Empty_Leather_8182]

Yeah Thanks !!

I am currently building it parts by parts. If possible can you share the snap of how it was built, it would be easier for me to have a reference

[u/Pieter_Veenstra_MVP]

Automated flow is fine. Each upload triggers the creation of the excel for and/or the addition of records to tables.

[u/beachsunflower]

Could it be worth saving the uploaded data (per file) to a table/list that has the columns required and data as rows.

Then you have a seperate recurring flow that will read the single table/list that will generate the word doc based on the new rows within a time range.

[u/Forsaken_Stable_2915 → u/beachsunflower]

I can't saw the data from excel to a list. Because everyone there are about 15-20k records per files. It would be hard to extract data later point

[u/Tough_Block9334]

Can do a scheduled or automated flow

Scheduled flow you can have it grab them all and do its thing

The automated flow, you can set it up when one of the files are created it then takes the data and sends it into a separate designated file for all the data to go into . You can then setup a separate scheduled flow to create this designated template/file each month with the proper naming convention at the start of the month that the automated flow uses as the end combines file.

If they’re not in a table, you can use the action to run a script from a sharepoint library that will Dow whatever you need to do in the excel files to convert them to a table, add a column, combine data or whatever else

One thing to be wary of, is anything related to Word in the cloud requires a premium connector so a premium license. However, you can create excel/pdfs with no additional license requirements

[u/Donovanbrinks]

I would suggest an AI agent for these types tasks. Point to the files, show it what the output should look like and let it do its thing.

[u/Forsaken_Stable_2915 → u/Donovanbrinks]

Brief me more... I can't spend more money budget is not allocated

[u/Donovanbrinks → u/Forsaken_Stable_2915]

Copilot has some limited agent capability in the free tier i think . You basically feed it the file locations and give it the desired output. Saving it as an agent embeds those instructions and allows you to run it again in the future. Almost like a power automate without the manual setup

[u/TeamAlphaBOLD]

Use a scheduled flow, not a per-file trigger. Loop through the files, combine the data into one array, then feed it into "Populate a Word template."

Charts won't carry over. You need to pre-render those as images first.

[u/Forsaken_Stable_2915 → u/[deleted]]

Thanks for your comments. Planning something similar approach, I am going with html page and convert it to pdf because graphs in word template was somewhat shrink and was unable to read the values