Power Automate: Copy Data from Master Excel to Client-Specific Files Based on Date

(1) Share

Report

Posted on by OshJaw

This is a bit complex to explain but any help is more than welcome..

We need to build a Power Automate flow to automate the process of extracting client-specific data from a main database file and transferring it to individual client tracker files stored in separate locations.

For example - Client A, B, C have their info on one sheet called Database. Now I copy the information from Database for client A and paste it into a separate folder made only for client 'A' called Client A Tracker.

For this. we want to build a Power Automate flow which essentially goes like this -

Powerautomate looks for a main file with a specific title and excel type when a file is added.
Retrieve content from the main Excel file that matches the required criteria.
Next, Power Automate should scan the Client ID column in the main database to identify relevant client records.
Then, based on the Client ID, Power Automate should search a separate folder to find the corresponding client tracker file.
Now this is where it gets more tricky, within the client tracker file, Power Automate should locate the row corresponding to the current date (the date column already exists in the tracker file). Finally, Power Automate should copy the relevant information from the main database and paste it into the corresponding row under the current date in the client tracker file.

So essentially we want powerautomate to copy information from one excel sheet to another sheet, check for the date and add it against that.

I haven't quite been able to build flow for points 1 to 3, let alone the next steps. I have attached an image of how far I have come.

If someone could help me with this or assist, it would be great. Any other alternatives are more than welcome.

Categories:

Power Automate

I have the same question (0)

All responses (2)

Answers (0)

Suggested answer

Michael E. Gernaey 53,360 Super User 2025 Season 2 on at

Like (3)

Report
Hi,

So a couple things

1) will your main file have a Table around the data?

2) will the secondary files have a Table around the data?

3) I am guessing that anytime this file exists, you want to paste the data to the client. Is this literally just one row or will a client have more than one?

4) is the master file getting overwritten? or just updated?

Some other things.

A) you didn't specifically say whether or not the file should check ALL client id's so if it has 50 rows in it, iterate through all 50 rows, and for each Row's Client ID, update the Client.

If the answer is, yes, just grab all the ides and iterate through them then we have extra work to do.

--You would have to NOT filter the Apply to each using the Client ID, just leave the filter off. This way it will loop through all ID's versus a specific 1. Removing this filter will give you that, not much work but a tiny tiny difference.

Since this is Excel it will be necessary

Now as for the steps, you actually called them out pretty well. So let me write them up as I understand them

Power automate looks for a main file with a specific title and excel type when a file is added.

You want to use this trigger

Retrieve content from the main Excel file that matches the required criteria.

In this case since its an Excel File you want to use

Now understand, this MUST have a table pre-existing around the data. If NOT, you have to use this Action first, to add One, either manually or using this action below AND you need to setup a Key (think primary key, so that the updates will point to the correct row)

Next, Power Automate should scan the Client ID column in the main database to identify relevant client records.

In this case, you should use a Filter, on the List Rows in a Table for instance
[ClientIDColumnAsAnUInt] eq InsertYourClientIdHere

If its a string then you need
[ClientIDColumnAsAString] eq 'InsertYourClientIdHere"

Then, based on the Client ID, Power Automate should search a separate folder to find the corresponding client tracker file.
After your List Rows, you will add An Apply to Each. All other actions go into this Action as its loops through whatever fields you need to add into the other field

Now I do not know how many rows you are updating. Whether its 1 or 50 this is the action to use so to loop through them

Now this is where it gets more tricky, within the client tracker file, Power Automate should locate the row corresponding to the current date (the date column already exists in the tracker file). Finally, Power Automate should copy the relevant information from the main database and paste it into the corresponding row under the current date in the client tracker file.

Now inside the Apply to Each
-Add another ListRows in a Table, and point it at the Clients Master File
-Make sure to use Filters to get only the rows you want (by Date, or ID etc)
-

Now in my Inner Apply to each I did NOT add a Create Table for the Client Excel Table. If it needs one, you will need to add it before or with the action.

So a Pseudo Version looks like this

Was this reply helpful? Yes No
OshJaw 17 on at

Like (0)

Report

@FLMike -

Let me first say how much I appreciate you taking the time and giving such a detailed response.

As for all the questions you have -

1. will your main file have a Table around the data?
No the main file or any of the files in this process actually don't have a table. Sad part if I cant tamper with the sheet because the workbook is interlinked.

2. will the secondary files have a Table around the data?

I am guessing that anytime this file exists, you want to paste the data to the client. Is this literally just one row or will a client have more than one?

Explained this below.

3. is the master file getting overwritten? or just updated?
Nope! the main concern for me is that for every cycle a new main sheet is uploaded and not overwritten. So the master file is a new workbook uploaded to a sharepoint folder which we then go to, find the relevant client information, copy the rows and then paste it in a separate workbook made for that specific client.

(there are few more rows. This is just sample date)

Each client has their workbook. The client workbook can be overwritten in the sense that, if master sheet is added on 02/19/2025, I copy the client information, head to the client workbook and copy paste the rows against this date - 02/19/2025.
In this example – Client ABC data has been updated below by copying data from master sheet.

4. you didn't specifically say whether or not the file should check ALL client id's so if it has 50 rows in it, iterate through all 50 rows, and for each Row's Client ID, update the Client

Yes, so we would the flow to go through all the client IDs, then based on the parallel flows added look for the relevant one with the client id and add it there. So at a time, the flow would be looking for 50 IDs and adding them to each individual client workbooks (50 workbooks).

Sample from another flow which has parallel flows added. Want to replicate something like this.

Also, powerautomate will check if there is any data for all the client IDs, recognize if there is then add to the client sheet and if not, leave it blank.
I think the images should give you a better idea of what the master sheet and client sheet looks like.

Was this reply helpful? Yes No