Power Automate List Rows Present in a Table Filter Query + 10 Examples

In this Power Automate tutorial, I will give an introduction to Power Automate list rows present in a table filter query, along with the below-provided examples:

• Power Automate list rows present in a table filter query contains
• Power Automate list rows present in a table filter query multiple conditions
• Power Automate list rows present in a table filter query startswith
• Odata filter query Power Automate Excel column with space
• Power Automate list rows present in a table filter query equals
• List rows present in a table filter query greater than Power Automate
• Power Automate list rows present in a table filter query not blank
• Power Automate Excel filter query and operator
• Power Automate list rows present in a table order by

Power Automate List Rows Present in a Table Filter Query

In Power Automate, the ‘List rows present in a table‘ flow action of Excel is similar to the Get items flow action of SharePoint.

This flow action functions so that we can easily fetch data from a huge data source in an Excel table based on the provided filter query condition.

Note:

The filter query currently supports the only single operators and functions like ‘eq’, ‘ne’, ‘contains’, ‘startswith’ or ‘endswith’ operators.
It does not support greater than, less than, and, or, can only support single operator. So, we can not create multiple filter query conditions.

Power Automate List Rows Present in a Table Filter Query Contains

To retrieve the string containing a particular substring from the Excel table column, follow the below example

Let’s see how to use the contains in the filter query of List rows present in a table-Excel.

Example:

I have an Excel file called Employee Details, from which we will use the Designation column to extract information that contains the ‘Developer’ as a substring value.

Important:

The Filter Query condition does not support the column name with any spaces, it should have to be single string. You can use Filter array flow action instead.

Let’s see how to use the contains in the filter query:

1. In Power Automate, select the Manually triggered flow.

2. Then, select List rows present in a table action, and then provide the parameters like Location, Document Library, File, and Table of Excel.

Click on Show all to display advanced options; in the Filter query field, write the below expression.

contains(Designation,'Developer')

3. Now, to check the fetched data, select Create HTML table action.

• From: Here, I have selected the body/values of the List rows present in the table output from dynamic content.

Click Show all to display all the advanced parameters.

• Columns: Select Custom column from the drop-down options.

Then, map the Excel value with the headers like below.

Now click on Save and run the flow manually.

4. To view the HTML table‘s result, take the Send an email(v2) flow action. Provide To, Subject, and Body.

5. Once the flow runs successfully, you can see that the output will only fetch the data that satisfies the filter query:

This is an example of Power Automate list rows present in a table filter query contains.

Power Automate List Rows Present in a Table Multiple Filter Query

As you know, Power Automate only supports the single operator in the filter query of a list present in a table flow action. So, you cannot use filter query multiple conditions in Power Automate to filter multiple columns.

If we create the Power Automate flow with multiple queries, the following error will be generated.

This is all about Power Automate list rows present in a table filter query multiple conditions to filter multiple columns.

Power Automate List Rows Present in a Table Filter Query Startswith

To fetch the Excel rows where the string starts with a specific character or a substring, follow the below example:

Example:

Here, I will take one more Excel sheet formatted as a table with information regarding Project Portfolio Details.

Refer to the image below:

I wanted to filter out the data in this Excel sheet where the Project Name starts with an ‘M‘ character.

Just check the steps that are mentioned in the above example:

1. Add List rows present in a table action, and then provide the parameters like Location, Document Library, File, and Table of Excel.

Change the filter query condition like this:

Startswith(ProjectName, 'M')

Note:

To filter the rows of an Excel table that ends with a specific character or a substring. Use the filter query like below: Endswith(columnname, ‘text’)

Then, save and run the flow.

Output:

The result will give the list of rows where the Project Name starts with a character ‘M‘, as you can see from the below image:

This is all about list rows present in an Excel table filter query using Power Automate.

ODATA Filter Query Power Automate Excel Column with Space

Unfortunately, the Power Automate Filter query in list rows present in a table action does not support the use of fields with spaces in the field name to filter the data. It is a limitation of the Power Automate filter query.

If you want to filter out the data with column names that contain spaces, you can use the filter array flow action with and, or even multiple conditions.

Power Automate List Rows Present in a Table Filter Query Equals

To filter the list rows present in an Excel table, check out the example below:

Example:

Here, I will use one of the Excel sheets that look like the below with the details:

Here, I wanted to filter out the data with whole rows where the Date column has Today’s date value.

Follow the above-given steps:

1. Select the List row present in a table-Excel online action, and then provide the Location, Document Library, File, and Table of the Excel sheet.

Filter Query: Date eq '@{utcNow('dd/MM/yyyy')}'

2. Then, select the Create HTML table action. Take the From value from the dynamic content and column section and map the Excel value to the header.

When we are retrieving the date from Excel, it will give a random integer number. So add the below expression:

addDays('1899-12-30',int(item()?['Date']),'yyyy-MM-dd')

Output:

The output will return the excel data with Date values as Today’s date.

Check the image below:

This is how we can use the equals operator in Power Automate list rows present in a table filter query.

Power Automate List Rows Present in a Table Filter query Greater Than

As we know, there is a known limitation for the filter queries in Power Automate list rows present in a table of Excel flow action. It does not support greater than(gt) and less than(lt) operators.

Power Automate flow will throw the error like this:

This is about the list rows present in a table filter query greater than in Power Automate.

Power Automate List Rows Present in a Table Filter Query not Blank

To filter out the rows that are not blank present in an Excel table, check the following example:

Example:

Here, I will use Excel data with a few columns, as shown in the screenshot below:

In this example, I wanted to filter out the table rows that are not blank based on the ‘Gender‘ column.

Here are the steps below:

1. Create Instant cloud flow in Power Automate.

2. Add List rows present in a table flow action. Set the parameters below:

• Location: Select the location of the SharePoint site or onedrive where the file is present.
• Document Library: Choose the specific document library.
• File: Select the Excel file present in the library.
• Table: Select the table name from the drop-down.
• Filter Query: Insert the below expression.

Note

In the filter query condition, ne represents not equals to operator. Where, it will filter out all the list rows present in a table that are not equal to the given value.

3. After that, add a Create HTML table flow action to present the data in a table format.

• From: Take the body/value of the Excel table from the dynamic content.
• Columns: Choose the Custom column and take the required fields that need to be displayed.

4. Then, take the Send an email(v2) flow action and set the parameters like To, Subject, and Body.

Save and run the flow.

Output:

The output array will display the data that do not have blank rows in an Excel table.

Refer to the image below:

This is how to retrieve the data, excluding the blank values from an Excel table, using the Power Automate filter query.

Power Automate List Rows Present in a Table Filter Query And

Power Automate list rows present in a table filter query only support the single operator to filter the Excel data. So, AND operator is not supported in the filter query condition to connect two queries.

The flow will generate the error like this:

Invalid filter clause: unsupported operation. Only single ‘eq’, ‘ne’, ‘contains’, ‘startswith’ or ‘endswith’ is currently supported.”

This is about list rows present in a table filter query of Excel in Power Automate.

Power Automate List Rows Present in a Table Order By

To sort the Excel list data in an ascending or descending manner based on using a filter query in Power Automate, follow the below-given example:

Example:

I will use one of the Excel sheets with a few columns having different data like below:

Here, my requirement is to sort the data in ascending order based on the Budget column.

Check out the following steps:

1. To arrange the data in ascending order, use the expression below to List rows present in a table.

Set the details like Location, Document Library, File, and Table.

• Order By: Insert the below expression.

Budget asc

Note:

To sort out the data in a descending manner, add the below given expression: columname desc

2. To display the details, add the ‘Create HTML table‘ flow action, which takes the body/values of list rows present in a table from dynamic content.

3. Then, add the Send an email(V2) action. Provide the To and Subject and format the Body using the HTML table’s outputs.

Save and run the flow manually.

Outputs:

When the flow runs successfully, you can see that the data has been sorted in ascending order based on the Excel column.

This is how to order the list rows present in an Excel table by using Power Automate.

Additionally, you may like some more articles:

• Power Automate Send Email On Specific Date
• Add Calendar in Excel Using Power Automate
• Power Automate Split String into Array
• Power Automate dayOfWeek Function
• Delete Files Older than 3 Years from SharePoint Document Library Using Power Automate

Conclusion

I hope this Power Automate tutorial explained how to work with list rows present in a table Power Automate filter query along with the below topics:

• Power Automate list rows present in a table filter query not blank
• List rows present in an Excel table filter query contains in Power Automate
• Filter query Excel equals Power Automate
• Filter Excel table startswith in Power Automate

Bijay Kumar

After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.