orange-juice.jpg

How to Merge Excel Table Data Using Microsoft Flow

(Editor’s note – Originally published July 23, 2018.  See updates at the bottom of the article.)

Microsoft Flow has been available for over a year as part of the Office 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.

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

Based on some feedback I’ve received, I wanted to provide a couple of updates on my original post.

First, I wanted to clarify item #2 in the “All Done” section where I said “Avoid adding total/subtotal rows to tables. This was causing the spreadsheets to be corrupted after the flow had run.”

I was specifically talking about a scenario in the “TeamResourcePlan” table where the Flow attempts to add records to a table that already has a “totals” row at the end of the table similar to the figure below:

This was causing erroneous results after Flow execution where some data rows were above the total line and others below. However, in the above examples, having a totals row in the source, i.e., project, spreadsheets did not cause an issue because the Flow was not trying to add rows to these tables.

Second, I’ve been asked about how difficult it would be to modify the Flow to support storage of the shared Team Resource Plan in a SharePoint list instead of a spreadsheet. The answer is…incredibly easy! First, you need a SharePoint list that contains the fields you’ll be merging from source spreadsheets:

Next, you just need to replace the Excel “Get rows” action with the SharePoint “Get items” action and replace the Excel “Insert row” action with the SharePoint “Create item” action:

These SharePoint actions are remarkably similar to the Excel actions in my original Flow. This illustrates how easy it is to adapt Flows as business requirements change. Note – I have an extra field in the SharePoint list named “title”. This is a standard field created “out-of-the-box” when I created the SharePoint list. Since it is a required field, I decided to store the project name and project team member name separated by a hyphen for this field, but I could have chosen any nonblank value.


Related Posts

Bob MorrisHow to Merge Excel Table Data Using Microsoft Flow

Join the conversation

This site uses Akismet to reduce spam. Learn how your comment data is processed.