Power Automate list rows present in a table filter query

In this Power Automate tutorial, we will discuss power automate list rows present in a table filter query. And also we will discuss the below points:

  • Power Automate list rows present in a table filter query
  • Power automate list rows present in a table filter query contains
  • Power automate list rows present in a table filter query date
  • Power automate list rows present in a table filter query greater than
  • Power automate list rows present in a table filter query and
  • Power automate list rows present in a table multiple filter query
  • Power automate list rows present in a table filter query not equal
  • Power automate list rows present in a table filter query equal
  • Power automate list rows present in a table filter query startwith

Power automate list rows present in a table filter query

The Power Automate excel ‘List rows present in a table’ action will fetch all data present in an excel table.

And if the excel file is larger, you don’t want to fetch all the data and you want a certain amount of information you need, for this, we have the option in ‘List rows present in a table’ action these are

  • Filter Query- By any rule we will filter the information, that we want.
  • Order by- Before the information come to us, we can order the result.
  • Top count- We can limit the number of information to be fetched.

So here we will use the Filter query which will parse the data and make the overall flow faster.

Currently, OData filter query operators are limited in Power Automate excel ‘List rows present in a table’ action.

  • endswith
  • startswith
  • ne
  • eq
  • contains

Also read, Power Automate get events into excel

Power automate list rows present in a table filter query contains

Here we will see how to use Power Automate list rows present in a table filter query contains operator.

The contains operator checks whether the collection or array of items contains a specific item or not. And if it contains it will return true else return false.

The syntax for contains operator is

contains('<collection>','<value>')

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

I have an Excel file called Employee information, from which we will use the Manager column to extract information that contains ‘Bijay’.

Microsoft Flow list rows present in a table filter query contains
Microsoft Flow list rows present in a table filter query contains

Now In Power Automate, select the Manually triggered flow. Then click on the Next step.

Microsoft Flow list rows present in a table filter query contains
Microsoft Flow list rows present in a table filter query contains

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

Then click on Show advanced options, in the Filter query field write the below expression.

contains(Manager, 'Bijay')
power automate list rows present in a table filter query contains
power automate list rows present in a table filter query contains

Now to see the filter results we will create an Html table, so click on the Next step and then select Create Html table action.

In the From Field select value from the dynamic content. Then click on Show Advanced options change the column field Advanced to customs. Then Map the excel value with the headers like below.

power automate list rows present in a table filter query contains
power automate list rows present in a table filter query contains

Now click on Save and run the flow manually. You can see the result in the output of create Html table.

power automate list rows present in a table filter query contains
power automate list rows present in a table filter query contains

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

Read Microsoft flow send email based on create date

Power automate list rows present in a table filter query date

Here we will see how to use the date column in Power Automate list rows present in a table filter query.

We will use the Employee Information excel table, in this excel table we will use the Hire date column, and then we will filter the employee who is hired today.

Microsoft Flow list rows present in a table filter query date
Microsoft Flow list rows present in a table filter query date

In Power Automate, select the Manually triggered Flow, then click on the Next step.

Microsoft Flow list rows present in a table filter query date
Microsoft Flow list rows present in a table filter query date

Select ‘List rows present in a table’ action and then provide the Location, Document Library, File, and table of excel table.

Then click on Show advanced options, in the Filter query field write the below expression:

HireDate eq 'fx(formatDateTime(utcNow(),'MM/dd/yyyy'))'
power automate list rows present in a table filter query date
power automate list rows present in a table filter query date

Now to see the filter result, we will create Html table, so click on the next step and then select the ‘Create Html table‘ action.

In From field, set the value from the dynamic content, then click on Show Advanced Options, and in the column field change the Automatic to custom. then map the excel value to the header. In the Hire date header, map the below expression:

if(empty(item()?['HireDate']),null,addDays('1899-12-30',int(item()?['HireDate']),'yyyy-MM-dd'))
power automate list rows present in a table filter query date
power automate list rows present in a table filter query date

Now click on save and run the flow manually, you can see the result in the output of create HTML table.

power automate list rows present in a table filter query date
power automate list rows present in a table filter query date

This is an example of power automate list rows present in a table filter query date.

Read Power Automate or Microsoft Flow check day of week

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

Here we will see how to use the greater than operator in the Power Automate list rows present in a table filter query.

As we know filter queries in Power automate list rows present in a table -Excel, does not support greater than(gt) operator. So we can’t use the greater than in filter query, otherwise, it will throw an error like below:

power automate list rows present in a table filter query greater than
power automate list rows present in a table filter query greater than

Power automate list rows present in a table filter query and

Here we will see how Power Automate lists rows present in a table filter query and operator.

Filter Query in the Power Automate list rows present in a table does not support AND operator. It only supports a single operation i.e. single eq, contains, ne, startwith or endswith.

Power Automate list rows present in a table multiple filter query

Here we will see how to use multiple queries in power automate list rows present in a table filter query.

As we know we cannot use AND and Or operator in the filter query, list rows present in a table. In filter query, it only supports single eq, contains, ne, startwith or endwith. So we cannot create multiple filter queries in Power Automate list rows present in a table.

Read Power Automate SharePoint Get Items Filter Query + 12 Examples

Power automate list rows present in a table filter query not equal

Here we will see how to use not equal(ne) operator in Power Automate list rows present in a table filter query not equal.

We will use the Employee information excel sheet, so in this sheet, we will use the Manager column, here we will filter Manager not equal to ‘Bijay’.

Microsoft Flow list rows present in a table filter query not equal
Microsoft Flow list rows present in a table filter query not equal

In Power Automate, select the Manually triggered Flow, and then click on Next step.

Microsoft Flow list rows present in a table filter query not equal
Microsoft Flow list rows present in a table filter query not equal

Select the List rows present in a table action, and then provide the location, Document library, File, and Table of the excel file.

Then click on Show advanced options, and in the Filter query field write the below query.

Manager ne 'Bijay'
Microsoft Flow list rows present in a table filter query not equal
power automate list rows present in a table filter query not equal

To see the result, we will create Html table, so click on the Next step and then select Create Html table action, In From select the value from the dynamic content.

Then click on Show advanced options, and in the column field change the Automatic to custom. Then map the excel value with the header like below. To format the date column i.e. Hire date, write the expression:

if(empty(item()?['HireDate']),null,addDays('1899-12-30',int(item()?['HireDate']),'yyyy-MM-dd'))
power automate list rows present in a table filter query not equal
power automate list rows present in a table filter query not equal

Now click on Save and run the flow manually, you can see the result, it filters out the ‘Bijay’.

power automate list rows present in a table filter query not equal
power automate list rows present in a table filter query not equal

This is how we can use not equal (ne) in power automate list rows present in a table filter query.

Read Power Automate send email to SharePoint group

Power automate list rows present in a table filter query equal

Here we will see how to use an equal operator in Power automate list rows present in a table filter query.

We have an excel called Employee information, in which we will use the Age column, and we will filter the employee data whose Age is equal to 30.

power automate list rows present in a table filter query equal
power automate list rows present in a table filter query equal

In Power Automate, select the Manually triggered flow, and then click on Next step.

power automate list rows present in a table filter query equal
power automate list rows present in a table filter query equal

Select List row present in a table-Excel Online action, and then provide Location, Document Library, File, and Table of excel. Next click on Show advanced options and then in the Filter query field write the below expression:

Age eq '30'
Microsoft Flow list rows present in a table filter query equal
power automate list rows present in a table filter query equal

Now to see the result we will create HTML table, so, click on the Next step and then select Create Html table action.

In from, select the value from the dynamic content, then click on Show advanced options, in the column field change the advanced options to custom. Then map the excel value with the header. In the Hire date header map the below expression:

if(empty(item()?['HireDate']),null,addDays('1899-12-30',int(item()?['HireDate']),'yyyy-MM-dd'))
Microsoft Flow list rows present in a table filter query equal
Microsoft Flow list rows present in a table filter query equal

Now click on Save and run the flow manually, so, you can see the result, filter the data and show the result of employee age is 30.

power automate list rows present in a table filter query equal
power automate list rows present in a table filter query equal

This is how we can use equal in power automate list rows present in a table filter query.

Also read, Power Automate remove characters from a string

Power automate list rows present in a table filter query startswith

Here we will see how to use the startswith function in Power Automate list rows present in a table filter query.

We have an excel sheet called Employee information, from which we will use the Full name column. Then we will filter the full name which starts with ‘S’.

power automate list rows present in a table filter query startwith
power automate list rows present in a table filter query startswith

In Power Automate, select the Manually triggered flow, then click on the Next step.

power automate list rows present in a table filter query startwith
power automate list rows present in a table filter query startswith

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

Then click on Show advanced options, in the filter query field write the below query.

startswith(Fullname,'S')
power automate list rows present in a table filter query startwith
power automate list rows present in a table filter query startwith

To see the result we will create an Html table, so click on the Next step and then select Create Html table action.

In From field set the value from the dynamic content. Then click on Show advanced options, in the column field change the Automatic to custom options. Then map the excel value with the header.

And in the Hire date header map the below expression.

if(empty(item()?['HireDate']),null,addDays('1899-12-30',int(item()?['HireDate']),'yyyy-MM-dd'))
Microsoft Flow list rows present in a table filter query startwith
Microsoft Flow list rows present in a table filter query startwith

Now click on Save and run the flow manually, and then we can see the result in the output in create HTML table, which starts with S.

power automate list rows present in a table filter query startwith
power automate list rows present in a table filter query startwith

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

You may like the following Power Automate tutorials:

In this Power Automate tutorial, we learned about Power Automate list rows present in a table filter query. And also we discuss the below points:

  • Power Automate list rows present in a table filter query
  • Power Automate list rows present in a table filter query contains
  • Power Automate list rows present in a table filter query date
  • Power Automate list rows present in a table filter query greater than
  • Power Automate list rows present in a table filter query and
  • Power Automate list rows present in a table multiple filter query
  • Power Automate list rows present in a table filter query not equal
  • Power Automate list rows present in a table filter query equal
  • Power Automate list rows present in a table filter query startswith
  • 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

  • >