Powerautomate multiple excel CSV/XLSX files into a consolidated excel workbook

[Anonymous]

Hello.  Is there a way to create a powerautomate solution that pulls in data of multiple monthly excel workbooks saved in Sharepoint, into a conslidated excel file, so as I add workbooks based on month (ex. Jan2022.xlsx, Feb2022.xlsx, etc.) into that SP folder, each new workbooks data gets populated into the consolidated workbook?

[hnguy71]

Hi @Anonymous ,

In theory it can... but it'll be a lot of work. Does the final output have to be a consolidated excel workbook? Have you thought about exploring dataflow or datamart?

If you still insist on working with Power Automate maybe something like this can work:

1. Recurring 1 Month

2. Open consolidated workbook

3. Check latest period in table

4. Filter for workbook where year-month is greater than latest period

5a. For each excel in output 

5b. Select table or sheet

5c. If output is more than limit, do an inner loop until you retrieve all records

6. Add rows to consolidated workbook

[Anonymous]

Thanks, this was definitely helpful.
I don't know much about the developent of a dataflow or datamart. So it would be a learning process.

What would be the advantages of either one?

[hnguy71 · Accepted solution]

@Anonymous ,

They're both cloud-based technologies that allows you to perform automated ETL tasks. The final output is saved on Azure Gen2 datalake where you can connect directly from Power BI to consume. They both can be scheduled to update based on your frequency liking. 

As a dataflow, you can share it and use it as a single source of truth.

A datamart is similar but gives you the flexibility to connect to it and build SQL queries to return only relevant data that you need per product.

[Anonymous]

Thanks very much!

[PaulDBrown]

If you need it to import into a PBI, and as long as all the files have the same struture, you can use the Sharepoint folder connector to import the files (no need to consolidate them into a single file; all you need is to keep adding files to the folder). PBI will import all the files included in the folder.

See this video