How to Retrieve and Merge Two Tables in Power Automate (Common Key Match)

In Power Apps, it’s easy to pull related data from two tables using the LookUp() function. But when you move to Power Automate, there’s no direct “LookUp expression” you can use in the same way.

This article focuses on a common real-world scenario: you have two different tables that share a common identifier, and you want to retrieve the matching data and combine it into one clean output. We’ll walk through a step-by-step approach to achieve this in Power Automate.

Example Scenario

Let’s take a simple example using Excel:

• Table 1: SKU, Description

• Table 2: SKU, Amount

Table 1(SKU, Description)

Table 2 ( SKU, Amount)

Our goal is to match the records using the common field (SKU) and create a single combined result like this:

Results, ( SKU, Description, Amount)

SKU | Description | Amount

By the end of this guide, you’ll be able to merge data from two tables based on a shared key—perfect for reporting, emails, approvals, or exporting combined data into a new Excel/SharePoint table.

Step 1: Create the Flow and Retrieve Data from the First Excel Table

Log in to the Power Automate portal and create a new flow (Instant, Automated, or Scheduled—based on your requirement).

Next, select the Excel Online (Business) connector and add the List rows present in a table action.This action will be used to retrieve data from the first Excel table (for example, the table containing SKU and Description).

Step 2: Map the Excel Location, File, and Table Details

In the List rows present in a table action, configure the Excel connection by mapping the required details:

• Location – Select where the Excel file is stored (OneDrive for Business or SharePoint)

• Document Library – Choose the relevant library (for SharePoint)

• File – Select the Excel file that contains the table

• Table – Choose the table name that holds your data (for example, SKU and Description)

Ensure that the Excel data is properly formatted as a Table with clear column headers. Power Automate can only read structured tables, not plain cell ranges.

Once configured, this action will successfully load all rows from the selected Excel table for further processing in the flow.

Repeat the same process to retrieve data from the second Excel table.

Step 3: Loop Through Records from the First Table (Apply to Each)

Now, we need to process each record from Table 1 individually.

From the Built-in controls, add an Apply to each action.In the Select an output from previous steps field, choose the value output from the List rows present in a table action of Table 1.

This configuration ensures that the flow loops through each SKU and Description record from the first table, allowing us to find and match the corresponding data from the second table in the next steps.

Step 5: Filter Matching Records from Table 2 Using SKU

Now that we are looping through Table 1, the next step is to find the matching record from Table 2 based on the common identifier (SKU).

Add Filter Array Action

Inside the Apply to each loop:

1. Go to Built-in → Data Operations

2. Select Filter array

Configure the Filter Array

From

• Select the value output from Table 2(List rows present in a table – Table 2)

Condition (Basic Mode)

• Left value: SKU (from Table 2 – dynamic content)

• Operator: is equal to

• Right value: SKU (from Table 1 – dynamic content inside Apply to each)

Advanced Mode (Recommended)

If you prefer using Edit in advanced mode, use the following expression(make sure to adjust field names if required):

@equals(item()?['SKU'], items('Apply_to_each')?['SKU'])

What This Does

• item()?['SKU'] → refers to the SKU from Table 2

• items('Apply_to_each')?['SKU'] → refers to the current SKU from Table 1

• The filter returns only the matching record(s) from Table 2

Step 6: Combine SKU, Description, and Amount into a Single Output Table

At this stage, we have successfully:

• Looped through each SKU from Table 1

• Retrieved the matching Amount from Table 2

Now, we’ll combine SKU, Description, and Amount into a single final table using an array variable.

Initialize an Array Variable

1. Go to Built-in → Variables

2. Select Initialize variable

3. Configure it as follows:

   • Name: FinalOutput

   • Type: Array

   • Value: (Leave empty)

This variable will store the final combined records.

Append Combined Data Inside the Apply to Each Loop

Inside the Apply to each loop (after the Filter array action):

1. Go to Built-in → Variables

2. Select Append to array variable

3. Configure:

   • Name: FinalOutput

   • Value:

{
"SKU": "@{items('Apply_to_each')?['SKU']}",
 "Description": "@{items('Apply_to_each')?['Description']}",
"Amount": "@{body('Filter_array')[0]?['Amount']}"
}

What This Does

• SKU and Description are taken from Table 1

• Amount is taken from the filtered result of Table 2

• Each iteration adds one combined record into the FinalOutput array

Final Result

The FinalOutput array will contain a combined dataset in this format

This final array can now be:

• Written back to Excel or SharePoint

• Sent as a formatted email

• Converted to HTML / CSV

• Used for reporting or approvals