Bob Morris is the Executive Director of Delivery at ThreeWill. Bob leads the delivery of all services which focus on helping clients leverage Microsoft Cloud technologies to provide innovative business solutions and drive real value through people-focused adoption programs.
Introduction on How to Merge Excel Table Data Using Microsoft Flow
Microsoft Flow has been available for over a year as part of the Microsoft 365 “ecosystem” of applications. It is an online workflow service that can automate workflows across a multitude of apps and services. Common uses of Flow include notifications, copying files, collecting data, and automating approvals. One interesting aspect of Flow is that it caters to both power users and developers. But, have you ever needed to do something more specific like merge excel table data?
Like others, I had seen multiple “cute little demos” that seemed pretty cool, but I was skeptical that I could leverage it for a “real world” business problem without a big investment of time. As the old saying goes, I wasn’t sure “the juice was worth the squeeze” – but I was wrong.
I decided to try and use Flow for a task that I encounter on a regular basis and I was pleasantly surprised at how easy it was to create something that really saves time and provides business value. I’m providing a blog on my experience to encourage other “doubters” to give Flow a try.
I work with a team of Project Managers, each managing multiple projects staffed from a common pool of people. We each maintain spreadsheets containing resource plans for number of hours staffed per person per week. We meet weekly to review demands for staffing on our projects and negotiate (hopefully) optimal project assignments for a pool of consultants.
A key tool used for this purpose is a team resource plan that combines all of the resource plans from our project spreadsheets. Currently, Project Managers execute a weekly ritual of manually merging their plans into this combined spreadsheet. I wanted to write a flow that would automatically merge a project resource plan into the combined team plan whenever the project plan was updated.
My source spreadsheet contains a table named “ResourcePlan” with a project name, person name, calendar week and number of hours:
My target spreadsheet for the combined team resource plan also contains a similar table (named “TeamResourcePlan”) containing similar entries for all projects:
In this example, I’m expecting to have data in my Project spreadsheet for project “ABC-01-03” replace any existing data for this project in the combined team resource plan. So, I expect to see hours for “John Doe” and “Jane Jones” in addition to “Fred Davis” in the updated team resource plan.
The Flow UI allows users to create workflows using a visual representation of steps as a series of “cards”. Each workflow consists of two main parts: 1) A trigger, 2) One or more actions. At a high level, my flow looks pretty simple:
I’m using the “OneDrive for Business – When a file is modified” trigger. I just need to provide the folder where my file is stored and then I use a “Condition” card to make sure that the file I’m dealing with is my project resource plan:
Before adding rows for my project to the combined team resource plan, I need to remove any existing rows for my project:
In the above Flow portion, I use the “Excel – Get Rows” action to retrieve all rows from the combined team resource plan and then an “apply to each” operation that loops through each row in the combined plan, confirms that the row corresponds to my project and, if it does, use the “Excel – Delete Row” action to delete the row from the table. Notice that I’m able to use information retrieved with the “Get Rows” action as parameters in each of the steps in this portion of the workflow.
After removing old rows from the combined plan, I now need to insert rows from my project resource plan. Similar to above, I first use the “Excel – Get Rows” action to retrieve all rows from my project file and then use an “apply to each” operation that loops through each row. I then insert the row into the combined team resource plan using the “Excel – Insert Row” action:
Again, note that I’m using information from the “Get Rows” action as parameters in both the “apply to each” operation and the “Insert Row” action. NOTE: When selecting parameters for this final action, be sure to select parameters from the “Get Rows” action for the project resource plan (not the combined resource plan):
Watch It Run!
Once you activate this flow, it will run anytime the project resource plan changes. For the above example, the next run of the Flow results in the following combined team resource plan:
One interesting side-effect of using Flow for Excel table operations is that Flow automatically adds a unique row ID for any tables used for its operations. This allows you, for example, to leverage a unique “RowID” as shown in the “Excel – Delete Row” action above.
That’s it – no more manual weekly update drudgery!
Like any new information services, flow has a few “edges”. I ran into two when creating the above Flow:
1) You can’t currently use formulas for any table values for tables you are updating.
2) Avoid adding total/subtotal rows to tables. This was causing the spreadsheets to be corrupted after the flow had run.
I tried to keep this flow as simple as possible. I know plenty of improvements could be made. However, I hope this blog post has encouraged any doubters to jump in and try to leverage a Flow to solve a business problem with real impact in their world. All it takes is one successful Flow to get your creative juices “flowing” (no pun intended ).
Updates from December 2018
This is very cool, Bob. I'm definitely going to try Flow to do something similar.
Hey Bob - is it possible to replicate this for a group of 25 projects without creating a flow for each project?
Hey Ken - Thanks for reading the post. I would see two problems to solve: 1) set up flow so that it is triggered for any project, and 2) identify project code for the related project. It seems like both of these problems could potentially be solved by adopting a strict file storage and naming approach where all project files are stored within subfolders off of a common root and project resource files always adhere to a name format that includes the project code needed for filtering the list within the shared resource file. With this approach you would need to update the flow trigger to leverage the "include subfolders" feature for the OnUpdatedFileV2 (when a file is modified) action. Next, you would need to parse the project code in the file name. This would make the flow more generic. This may not be trivial to implement. So, unless you have a huge number of projects, this might not be worth the effort. One benefit of using separate flows is more control over when/if project information gets into the shared resource file.
Hi, I have used a similiar flow, I am adding the total contents of my file in sharepoing folder to to and archive file in the same sharepoint folder so I have the history of all projects over time. There are 1922 lines it seems the bigger the archive file gets the longer the update takes. Is there no way to copy the complete table from the first file into the archive in one go?
Hi Bob. Thanks for this tutorial. Would you happen to know how I can combine 4 different table in 4 different files into 1 table in 1 separate file?