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.

List rows present in a table filter query Power Automate

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.

See also  How to Check Condition Contains String in Power Automate?

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

contains(Designation,'Developer')
Power Automate list rows present in a table filter query

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.

List rows present in a table Power Automate filter query

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.

Power Automate list Excel rows filter query

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

List rows present in a table Power Automate

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.

Power Automate list rows present in a table multiple filter query

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:

Power Automate list rows present in a table filter query startwith

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’)
List rows present in a table select query starts with Power Automate

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:

Power Automate list rows filter query startswith

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.

See also  How to Get the First Character from a String in Power Automate?

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:

Filter query list rows present in excel table Power Automate

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')}'
Filter query excel Power Automate

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')
Filter query list rows present in table in Power Automate

Output:

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

Check the image below:

Filter query Power Automate excel filter date

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:

Power Automate list rows present in a table filter query greater than

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:

Power Automate list rows filter query

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.
Power Automate list rows present in a table filter query not blank

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.
Power Automate filter query not blank list rows present in table

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

Filter rows not empty in list rows Power Automate

Save and run the flow.

See also  How to count rows in an Excel table using Power Automate?

Output:

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

Refer to the image below:

Power Automate excel filter query

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.”

Power Automate List Rows Present in a Table Filter Query And

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:

Power Automate list rows present in a table order by

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
List rows present in a table order by Power Automate

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.

Power Automate list rows present in a table order by ascending

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

Additionally, you may like some more articles:

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
  • Thank you for the very useful information,
    I have a question: how can i use (Contain) expression to get all rows with values <8 "less than eight or any number" filtered?
    i tried CONTAIN('COLUMN NAME', '<8') but didn't work

  • Hi!
    How do i filter file if its dynamic. In this example, what i wish to obtain in 1 file per department. Si output should be 2 files (1file of IT with 2 lines , 1 file of HR with 1 line)

    THank you!

  • >