Bob Morris

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.

Business Problem

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.

Source/Target Spreadsheets

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.

My Flow

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:

The Trigger

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:

First Action

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.

Second Action

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.

All Done

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