cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
v-padelr
Level: Power Up

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

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!

4 REPLIES 4
Community Support Team
Community Support Team

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

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:23.JPG

The folder structure of my OneDrive folder as below:22.JPG

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:24.JPG

 

25.JPG

The flow works successfully as below:26.JPG

 

 

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

Use expression in flow actions

 

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-padelr
Level: Power Up

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

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

v-padelr
Level: Power Up

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

Hi Kris (@v-xida-msft)

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

results.PNG

This is what i put in the action 

input.PNG

 

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!

 

Administrator
Administrator

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

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

-Gabriel
Microsoft Power Automate Community Manager
Are YOU a member of your local Power Automate User Group?
Fill out This Form to claim your Community User Group Member Badge!

Helpful resources

Announcements
firstImage

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

firstImage

Coming Soon: T-shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

firstImage

Incoming: New and improved badges!

Look out for new contribution recognition badges coming SOON!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Users online (6,045)