Filter and copy data with Power Automate

Article
03/10/2023
5 minutes to read

This tutorial shows you how to create a cloud flow that monitors a source for new or changed items and then copies those changes to a destination. You may create a cloud flow like this one if your users enter data in one location, but your team needs it in a different location or format.

While this tutorial copies data from a Microsoft SharePoint list (the source) to an Azure SQL Database table (the destination), you can copy data among any of the more than 900 connectors that Power Automate supports.

Tip

For detailed information about using SharePoint with Power Automate, go to the SharePoint documentation.

Prerequisites

Access to a data source and a destination. This tutorial doesn’t include steps to create the source and destination.
Access to Power Automate.
A basic understanding of how your data is stored.
Familiarity with the basics of creating flows. You can review how to add actions, triggers, and conditions. The following steps assume that you know how to perform these actions.

Tip

Every column name in the source and destination doesn't need to match. However, you must provide data for all required columns when you insert or update an item. Power Automate identifies the required fields for you.

Quick overview of the steps

If you're comfortable with Power Automate, use these quick steps to copy data from one data source to another.

Important

Changes you make in the destination aren't copied to the source because two-way syncs aren't supported. If you attempt to set up a two-way sync, you'll create an infinite loop where changes are sent endlessly between the source and destination.

Identify the source you'll monitor and the destination to which you'll copy changed data. Confirm you've access to both.
Identify at least one column that uniquely identifies items in the source and destination. In the example that follows, we use the Title column, but you could use any column(s) you want.
Set up a trigger that monitors the source for changes.
Search the destination to determine if the changed item exists.
Use a Condition like this:
- If the new or changed item doesn't exist in the destination, create it.
- If the new or changed item exists in the destination, update it.
Trigger your flow, and then confirm that new or changed items are being copied from the source to the destination.

If you haven't created a connection to SharePoint or Azure SQL Database previously, follow the instructions when you're prompted to sign in.

Here are the detailed steps to create the flow.

Monitor the source for changes

Sign in to Power Automate.
Select Create > Build your own.
In the Choose a connector list, select SharePoint.
In the Choose a trigger list, select When an item is created or modified.
Select Skip to building your own flow.
Enter the Site Address and then select the List Name on the When an item is created or modified card.

Provide the Site Address and List Name for the SharePoint list that your flow monitors for new or updated items.

Search the destination for the new or changed item

We use the Get rows (V2) SQL Server action to search the destination for the new or changed item.

Select New step.
Search for Get rows, select Get rows (V2) SQL Server in the Actions list.
Select Show advanced options.
From the dropdown menus, select the Server name, Database name, and the Table name of the table that you want to monitor.
In the Filter Query box, enter Title eq ', select the Title token from the dynamic content list, and then enter '.

The previous step assumes you're matching the titles of the rows in the source and the destination.

Check if the new or changed item was found

We use the Condition action to check if the new or changed item was found.

Select New step and select Condition in the Actions list.
Select the box on the left.

The Add dynamic content from the apps and connectors used in this flow list opens.
From the Get rows category, select value.

Tip

Confirm you've selected value from the Get rows category. Don't select value from the When an item is created or modified category.
From the list in the center box, select is equal to.
In the box on the right, enter 0 (zero).
Select Edit in advanced mode.

When advanced mode opens, you see @equals(body('Get_rows')?['value'], 0) expression in the box. Edit this expression by adding length() around the body('Get_items')?['value'] function. The entire expression now appears like this: @equals(length(body('Get_rows')?['value']), 0)

Tip

Adding the length() function allows the flow to check the value list and determine if it contains any items.

When your flow "gets" items from the destination, there're two possible outcomes.

Outcome	Next step
The item exists	Update the item
The item doesn't exist	Create a new item

Note

The images of the Insert row and Update row cards shown next may differ from yours because these cards show the names of the columns in the Azure SQL Database table that's being used in the flow.

Create the item in the destination

If the item doesn't exist in the destination, create it using the SQL Server - Insert row action.

On the If yes branch of the Condition:

Select Add an action, search for insert row, and then select SQL Server - Insert row.

The Insert row card opens.
From the Table name list, select the table into which the new item will be inserted.

The Insert row card expands and displays all columns in the selected table. Fields with an asterisk (*) are required and must be populated for the row to be valid.
Select each column that you want to populate and enter the data.

You may enter the data manually, select one or more tokens from the Dynamic content, or enter any combination of text and tokens into the columns.

Update the item in the destination

If the item exists in the destination, update it with the changes.

Add the SQL Server - Update row action to the If no branch of the Condition.
Follow the steps in the create the item section of this document to populate the columns of the table.
At the top of the page, enter a name for your flow in the Flow name box, and then select Create flow to save it.

Now, whenever an item in your SharePoint list (source) changes, your flow triggers and either inserts a new item or updates an existing item in your Azure SQL Database (destination).