Power Platform Community Forum Thread Details

Good Morning,

I am sad to say I cannot figure this out. It seems like it is a common situation, but has a few nuances.

Multiple spreadsheets are emailed to me each day, and the data has to be consolidated into one master workbook. I have successfully setup a flow to download the attachments to sharepoint as they are received. I cannot seem to figure out the consolidation of the information into the master workbook though.

The problem is the master sheet has one tab corresponding to each source sheet, so all of the data does not get pasted to one table. There are 10 source sheets, and 10 tabs on the master sheet. Another curveball is the source sheets are not exactly the same format. They are similar. Some are actual tables, some are not. I have macros to convert a few of them into tables. I could create macros for the remaining if necessary, I don't know if the source data has to be in a table. They are all table format, meaning columns, headers and rows, just not an actual table.

The section that the source data is pasted into ARE actual tables. So source sheet A gets pasted into the table on tab A of the master sheet, source sheet B gets pasted into the table on tab B of the master sheet and so forth.

Ideally it would be a scheduled task for the same time each day. I'm not sure what other info would be needed to assist, but any help would be greatly appreciated!

Categories:

Building flows

There is an excel Create a Table action in power automate. You could use that to format the source data that is not in tables, then copy/addrows into your master table.

It is simple to add rows from an excel table into another excel table, but it would be tough to have a single flow that accommodates all of the files and table types. There would need to be conditions that has separate branches for different file/table types. Like if file is named Invoices, then do this, or if files is named Receipts then do this. Or you could base your rules on different folders, so have a folder where the source file does have a table so it could be an easy flow to get table and add row to master. Then have a different folder for complicated no table sources and have a more complicated flow for those files.

I don't think I would use a scheduled flow, but an individual flow for each file when it is added. A scheduled flow and a single flow would be very similar, but at least the single flow would only deal with one file at a time whereas a schedule flow might add some complexity dealing with pulling all files and apply to each.

I think I would group my files by type in separate folders and then make a flow for each file type when it is added. So easy source table to master table files in the Easy Folder using Easy Flow. Then another flow or flow(s) to manage more complicated transforming files.

You could use the Solutions flow so that there is a parent flow that checks the file for whatever type or method you need and then it calls a child flow to deal with that type of file.

Just brainstorming, it's difficult to work with unstructured data, hope this helps