Getting the latest row from multiple excel workbooks and merging it into a master workbook

[v-padelr]

Hi

I'm rather new to Flow and this community. I was wondering if anyone could help me with this issue. 

I have around 76 workbooks located in a personal OneDrive. Here are the steps i need to perform

1. When a file is modified in the onedrive the flow will kick in
2. The flow will look at the updated file and take the latest row and copy it into a file called Master
3. An email notification will be sent out to several people and notify them that there is a change and they need to review

• The file names are all different (it lists out country names in EMEA) and the worksheets have the same name as the file name. There can be 1 or 2 worksheets in a file depending on the name.
• There are no duplicates in each file 
• The structure is the same for all files, even the master

I tried using Power Query initially, but i keep getting an error saying that my credentials do not allow that type of URL. I can't use PowerBi as my credentials won't let me do it (i'm a contractor btw). 

ANy help will be gretly appreciated! I've been racking my brain for the past 3 days with this. Also if you can provide a step by step guide that would be awesome, I'm a total newbie when it comes to this (this is my first time ever being exposed to Flow). Thank you!

[v-xida-msft]

Hi @v-padelr,

Could you please share a bit more about your Excel files?

I have created some Excel files on my side and the data structure of it as below:

The folder structure of my OneDrive folder as below:

I have made a test on my side and please take a try with the follwoing workaround:

• Add a "When a file is modified" trigger, specify Folder.
• Add a "Get rows" action, File anme set to File identifier dynamic content of the trigger, Table name set to Table1.
• Add a Compose action, Inputs set to following formula:

last(body('Get_rows')?['value'])

• Add a "Apply to each", input parameter set to following formula:

array(outputs('Compose'))

• Within "Apply to each" action, add a "Insert row" action, specify File Name (Master.xlsx) and Table Name set to Table1 as a custom value. The ProjectName field set to following formula:

item()?['ProjectName']

Start Time field set to following formula:

item()?['Start_x0020_Time']

End Time field set to following formula:

item()?['End_x0020_Time']

Executor field set to following formula:

item()?['Executor']

Note: The PerojectName, Start Time, End Time and Executor are all columns in the Excel table of my Excel files. On your side, you should type the following formula to reference the column values within your Excel table:

item()?['ColumnNameOfYourExcelTable']

If there is a space within the column name, please replace the space with '_x0020_'.

Image reference:

The flow works successfully as below:

More details about using expression in flow actions, please check the following formula:

Use expression in flow actions

Best regards,

Kris

[v-padelr]

Awesome! Thank you so much Kris (@v-xida-msft)! I will try this out ASAP! Happy Friday!

[v-padelr]

Hi Kris (@v-xida-msft)

I tried your flow but once i got it to finish running i got these results:

This is what i put in the action 

ALso i just got new requirements and i was hoping you can help me again.

• The Master file will be blank initially, so the first time the flow will run it needs to populate the master 
• The 76 country workbooks are already populated, users will just be editing a specific row
• If a row will be updated with new informaiton the master needs to be updated

THank you so much for the help @v-xida-msft!

[GabrielStJohn]

Hello, @v-padelr!

Thank you for posting to the Microsoft Flow Community forums! I see that in one of the above replies you were offered a solution to your issue! If you feel that the reply offered an applicable and satisfactory solution, please click "Accept as Solution" so that other users can find this information and utilize it easily!

Thank You!

-Gabriel

Flow Community Manager