How to merge/ join excel files in Power Automate flow

Welcome everyone to my blog. In today’s blog, I will discuss how you perform join on excel files using Power Automate flow.

Through my blog I share interesting tips and discuss on latest releases of Microsoft.NET technologies, Dynamics 365 and Power Platform, SharePoint and Client scripting libraries. Please subscribe to my blog to stay updated.

Let’s jump into the problem statement. I have couple of excel files. One for Employees and the other for Department. Sample illustration below.

Not a very difficult problem statement after all. We just need to merge the excel files and bring in the Department name column in the final dataset. In simple terms, we need to join these two excel files based on a Department ID column.

Unfortunately, as of the time of writing the blog, there is no provision to merge excel files. However we can easily accomplish this. Let’s see the steps involved.

The first step is to get the list of rows from Employee excel. For this we will use the ‘List rows present in a table’ action of the Excel Online connector.

Remember the List rows present in a table retrieves a max of 256 rows at a time. If you want to retrieve more than 256 rows in one query, please check this blog.

The next step is to create a JSON array from the excel output. For this I used the Select step of the Data Operation connector and mapped the properties.

As you see in the illustration above, I have mapped the Excel columns to the JSON properties and since I need Department Name in the final output, I have kept a property with the same name but haven’t set any value yet.

What’s next? Obviously the data from the Department excel file. Quite obvious I used the same ‘List rows present in a table’ action to get the data from the Departments excel.

Now the most important part. The part where I need to merge. To do this, I used the Filter array action of the Data Operation connector. I have renamed it to ‘Find matching department’.

Here are the next step of actions.

Seems complex? Don’t worry. I am going to explain each of the steps.

• Iterate through the list of employees created through the Select operation.
• Find the matching department from the values in Department excel file. For this I used the Filter array action.
• For each matching department name (here as per the data, we will always get one record. Modify your logic if you are expecting more than one record), I first update the Department Name property of the employee object. Below is the expression. I use the setProperty expression for that.

setProperty(items('For_each_Employee'), 'Department Name', items('Foreach_matching_department')['Department Name'])

• Using the output of the Compose action, I insert a row in a separate excel file.

Formula for each property specified below.

Employee ID – outputs(‘Set_Department_name’)[‘Employee ID’]
Employee Name – outputs(‘Set_Department_name’)[‘Employee Name’]
Department Name – outputs(‘Set_Department_name’)[‘Department Name’]

And that’s it. You should be done. When I run the flow, I am able to see all the values in the Merged excel file.

Hope this helped. For similar interesting topics on Microsoft.NET and Power platform, please subscribe to my blog.

Debajit Dutta
Business Solutions MVP