Do you need to check if a row already exists in your Excel table with Power Automate?

Checking for the existence of specific data can be a vital part of your processes.

This can be particularly useful for situations where you want to perform an action based on the presence or absence of specific information in your Excel table.

For example, if the row already exists you might need your flow to update the data instead of adding a new row to your table.

In this article, you will learn how to use Power Automate to check if a row exists in an Excel file.

Excel Tables in Power Automate

Power Automate mainly works with Excel Tables. An Excel table is a way to store and organize data in a tabular format.

Power Automate allows you to add, update, or delete data from your Excel tables with a variety of built-in actions.

You can use actions like Get a row or List rows present in a table to fetch data from your tables by specifying the location of the file in your SharePoint site.

Key Column and Key Value in Excel Tables

Some of the Excel actions, such as the Get a row action in Power Automate, require a key column and a key value to retrieve data.

The key column is the column that contains data that uniquely identifies each row, and the key value is the unique value for each row. This allows you to uniquely identify and retrieve a specific row from your table.

Check If Row Exists Using List Rows Action

This method will allow you to check if a row exists in your data based on a column that might contain duplicates.

This will use the List rows present in a table action to return all rows that match your criteria.

Then if the action returns at least one row, the row exists.

List Rows Present in a Table Action

You will need to start by adding the List Rows Present in a Table action to your flow to check if a row exists in an Excel sheet using Power Automate,

This action retrieves all rows from a specified table. Then you can use the ODATA filter query option to narrow down the rows based on your criteria.

1. Add the List rows present in a table action to your flow.
2. Select the Location of your Excel file. This is the SharePoint site where your file is stored.
3. Select the Document Library within the SharePoint site where your Excel file is located.
4. Select the File.
5. Select the Table within your file in which you want to check if a row exists.
6. Click on the Show advanced options link at the bottom of the action. This will reveal more options including the Odata filter query option that will allow you to return only rows that match a given criteria.

Now you will be able to add a Filter Query to the action to limit the rows that are returned based on your criteria.

YourColumnName eq 'YourColumnValue'

7. Add the above to Filter Query to your List rows present in a table action. For example, to get all the rows containing BMW in the Make column, the filter query would be Make eq 'BMW'.

⚠️ Warning: The Odata filter query does not support column names with spaces in Excel. Remove any space characters from the column heading that you want to filter on!

Your filter query criteria can use dynamic content. For example, if the value you want to filter on is inside a compose action, the filter expression would be Make eq '@{outputs('Compose')}'.

Also, make sure the criteria value is encased in single quote characters '.

Condition Action to Check If Anything Was Returned

Now you will be able to test if anything was returned from your List rows action.

The action will either return an array of all the rows matching your criteria, or it will return an empty array [] if there are no matches.

Now you can add a Condition action after the List rows present in a table action to evaluate the results.

This will use the length function in the expression to check the number of returned rows. If the length is greater than 0, it means a matching row exists.

1. Add the Condition action to your flow.

length(outputs('List_rows_present_in_a_table')?['body/value'])

2. Click into the left field of the condition and add the above expression. This will return the number of rows returned.
3. Select the is greater than or equal condition option.
4. Add the value 0 in the right side of the condition.
5. Add any actions you want to run if the row exists into the If yes part of the Condition action. Add any actions you want to run if no rows exist in the If no part of the Conditon action.

In the If yes branch of the condition, you can add further actions based on your requirements, such as updating or processing the existing row.

For the If no branch, you can add actions for when the row doesn’t exist, like creating a new row with the desired values.

You can also leave either of these empty if you don’t want any actions to run.

Check If Row Exists Using Get a Row Action

If your Excel table contains a key column and key values, and you want to check if a row exists based on this column, then you should use the Get a row action.

This will avoid the Odata query and allow you to either return nothing or a single row from your table.

Get a Row Action

First, you will need to add the Get a Row action to your flow.

1. Add the Get a row action to your flow.
2. Select the Location of your file.
3. Select the Document Library where the file resides.
4. Select the File.
5. Select the Table in the file.
6. Select the Key Column for your table. This is a column in your Excel table that contains unique values.
7. Enter a value that you want to check exists in the table in the Key Value field.

If the row with the Key Value exists, the action returns the data in the row. But if the row does not exist the action will error.

This means you will need to use the Configure Run After options to ensure your flow continues to run when the action results in an error.

Configure Run After

You will need to configure the Run After settings in the action directly below the Get a row action.

1. Add the Condition action to your flow directly below the Get a row action.
2. Click on the Ellipses icon on the right side of the Condition action.
3. Select the Configure run after option in the settings menu.

This will open the Configure run after settings.

4. Check both the is successful and has failed options. This will ensure the flow continues to run when the Get a row action results in an error because no Key Value is found.
5. Press the Done button.

Now your Condtion action will run regardless if the Get a row action returns an error or not.

Condition Action to Check If Anything Was Returned

Now you can configure the Condition action. This will be a bit different than previously seen.

1. Add the dynamic content for the Key Column field from the Get a row action to the left side of the condition.
2. Select the is not equal to condition option.
3. Enter the null expression on the right side of the condition. Note, this needs to be entered as an expression and not just the text null.
4. Now you can add actions to the If yes and If no branches of the condition.

When the row exists in the Excel table, the Get a row action will return a value in the dynamic content and therefore is not null. This results in the If yes branch executing.

If the row does not exist in the Excel table, the Get a row action will error. The Condition action runs after and the dynamic content from the Get a row action is null. This causes the If no brach of the condition to run.

Conclusions

Checking for the existence of a row in an Excel table is possible using Power Automate.

This can be done with either the Get a row or List rows present in a table depending on if the value you’re looking for in the table is unique or not.

Condition actions for each of these scenarios can then be set up to test if the item exists.

Have you needed to check if a value exists in your Excel data? How did you solve this in your flow? Let me know in the comments.