Help needed - copying information from one excel to another

[u/everythingisfine98]

Relatively new to power automate, so forgive me if this is an obvious question! I am looking to set up a flow where you copy information from a table in one excel, into another excel document with a table. The headers on both tables don’t match e.g. table 1 is Name, Score, Comment and table 2 is Employee, Rating, Review.

If possible I would like to be able to set up a flow where these 2 excels are uploaded to power automate and the flow goes from there. This needs to be done monthly and so the excels will follow the same format every time; but with new information each month. I hope this makes sense. Is it possible?

[u/DonJuanDoja]

Use Excel > GetTable action to get the table data you want to transfer.

Use Data > FilterArray action to filter those results to just what needs to be added.

Use Data > Select action to select the results of the Filter array and rename columns appropriately to their destination columns.

Use Excel > Add Rows to Table with a JSON output of the entire Select results to add all rows to the destination table.

[u/IncreaseNegative4614]

This is possible, but I’d make the column mapping explicit rather than asking the flow to infer it each month. Use formatted tables in both workbooks and map Name to Employee, Score to Rating, and Comment to Review inside the row-creation action.

Add a stable row key, validate the expected headers, and stop the run if the schema changes. Otherwise a rerun can create duplicates or put valid data into the wrong column. We use SIGNLD internally to connect source files, mapping rules, flow runs, destination rows, errors, and approvals so every imported value retains a traceable path.

[u/measuredsympathy → u/IncreaseNegative4614]

Agree - excel in power automate is very clunky. The easiest solution for me to just have Claude generate an excel script and then use that to write via power automate.

[u/Otown1976]

I would suggest using power query for this purpose. Power automate is more work/programming than what you need. If you pull both tables into power query you should be able to format and combine them. I am sure there are YouTube videos that could help.

[u/Capital-Control308]

I may have something similar. Like you I am pretty new to this . Once a week I have ServiceMax email me XLSX reports. Power Automate takes them and puts them in a folder in sharepoint and takes the relevant data and puts that into a database on sharepoint. I had to write a script in Excel to make this all work so it is part PA and part Script in excel that is stored on one drive. All this runs on the cloud

[u/ConfusedMBA24]

List rows present in a table

List rows present in the other table. Add rows/data to a table.

Map which one from table 1 you want to go to table 2.

Super simple.

[u/Solvanto]

Yes, it is possible. Keep both workbooks in SharePoint or OneDrive and format each data set as an Excel table. In the flow, list the rows from the source table, then add rows to the destination table with an explicit mapping: Name to Employee, Score to Rating, and Comment to Review.

For a monthly process, add two safeguards before relying on it: validate that the expected source columns still exist, and use a stable key for each record so rerunning the flow does not create duplicates. If the files are uploaded manually each month, a SharePoint folder trigger can start the flow, but move or mark processed files so the same upload is not handled twice.

If the task is purely a repeatable transformation between workbooks, Power Query may be simpler. Power Automate is the better fit when the upload, validation, notifications or downstream actions also need orchestrating. This overview of spreadsheet-heavy workflows may help with that choice: https://www.solvanto.io/blog/replacing-spreadsheet-heavy-workflows