How To Filter Excel Table Rows In Power Automate: Text Numbers, Dates

How To Filter Excel Table Rows In Power Automate: Text Numbers, Dates

If you use the Power Automate Excel action to list rows present in a table you will notice an option to filter the result. In this short guide I will show you how to filter Excel table rows in Power Automate. I’ll share examples of how to write odata queries to filter text values in a spreadsheet. Additionally, I will also provide a workaround for filtering number and date data types which cannot be handled using odata queries.

Table of Contents

• Create An Excel Table In A SpreadsheetFilter An Excel Table Text Column In Power AutomateText Column EqualsText Column Does Not EqualText Column ContainsText Column Starts WithText Column Ends WithFilter A SharePoint Number Column In Power AppsNumber Column Is Equal ToNumber Column Is Greater Than Or Less ThanNumber Column Is Between Two ValuesFilter A SharePoint Date Column In Power AppsDate Column Is Equal ToDate Column Is Greater Than Or Less ThanDate Column Is Between Two Values




Create An Excel Table In A Spreadsheet

Spreadsheet data must be inside of an Excel table for Power Automate to use it. To convert a range into a table, place the cursor inside of the range and select the Table button from the menu.



When the Excel table is created a new tab appears on the menu called Table Design. Place the cursor inside of the table and update the table name to tblSales.



For Power Automate to open the Excel file it must be stored in either SharePoint or OneDrive. Go to SharePoint and create a new Document Library named Sales Team. Then drag and drop the Sales Transactions spreadsheet into the Document Library.



This data can be copied into your own spreadsheet if you’d like to practice filtering Excel data with Power Automate.

TransactionDateCustomerAmount
1/1/2023Jacks Meat Shop310
1/3/2023Bridgewater Mill700
1/3/2023Jacks Meat Shop650
1/3/2023Green Valley Dairy890
1/4/2023Bridgewater Mill330
1/4/2023Friendly Farms1000
1/4/2023Green Valley Dairy290
1/5/2023Bridgewater Mill930
1/5/2023Jacks Meat Shop210
1/6/2023Friendly Farms710
1/6/2023Green Valley Dairy930
1/6/2023Friendly Farms810




Filter An Excel Table Text Column In Power Automate

Excel tables can be filtered by writing an ODATA query. With ODATA queries we can use the following operations: equals, does not equal, contains, startswith, endswith.



Text Column Is Equal To

Write this ODATA query to filter the Excel table where the Customer column is equal to Friendly Farms.



Filter Query:

Customer eq 'Friendly Farms'



Output array:

TransactionDateCustomerAmount
1/4/2023Friendly Farms1000
1/6/2023Friendly Farms710




Text Column Does Not Equal

Use this ODATA query to filter the Excel table where the Customer column does not equal Friendly Farms.



ODATA Filter Query:

Customer ne 'Friendly Farms'



Output Array:

TransactionDateCustomerAmount
1/1/2023Jacks Meat Shop310
1/3/2023Bridgewater Mill700
1/3/2023Jacks Meat Shop650
1/3/2023Green Valley Dairy890
1/4/2023Bridgewater Mill330
1/4/2023Green Valley Dairy290
1/5/2023Bridgewater Mill930
1/5/2023Jacks Meat Shop210
1/6/2023Green Valley Dairy930
1/6/2023Bridgewater Mill810




Text Column Contains

Input this ODATA query to filter the Excel table where the Customer column contains a substring with the word Shop.



ODATA Filter Query:

Customer contains 'Shop'



Output Array:

TransactionDateCustomerAmount
1/1/2023Jacks Meat Shop310
1/3/2023Jacks Meat Shop650
1/5/2023Jacks Meat Shop210




Text Column Starts With

Write this ODATA query to filter the Excel table where the Customer column starts with the word Bridge.



ODATA Filter Query

Customer startswith 'Bridge'



Output Array:

TransactionDateCustomerAmount
1/3/2023Bridgewater Mill700
1/4/2023Bridgewater Mill330
1/5/2023Bridgewater Mill930
1/6/2023Bridgewater Mill810




Text Column Ends With

Use this ODATA query to filter the Excel table where the Customer column ends with the word Dairy.



ODATA Filter Query:

Customer endswith 'Dairy'



Output Array:

TransactionDateCustomerAmount
1/3/2023Green Valley Dairy890
1/4/2023Green Valley Dairy290
1/6/2023Green Valley Dairy930




Filter An Excel Table Number Column In Power Automate

Number columns in an Excel table cannot be filtered properly using an ODATA query because they appear as text. The solution is to convert those columns to a number after the data has been retrieved from the file. Then we can use all of the expected operators to filter – equals, does not equal, greater than, less than, greater than or equal to, less than or equal to and does not equal.




Number Column Is Equal To

Create these actions in Power Automate with the following values:

  • Excel for Business – List rows present in a table
  • Data Operations – Select
  • Data Operations – Filter Array



Power Automate Expressions:


Select: Convert Amount To Number

float(item()?['Amount'])



Filter array: Amount Eq 1000

item()?['Amount']



Output array:

TransactionDateCustomerAmount
1/4/2023Friendly Farms1000




Number Column Is Greater Than Or Less Than

Use these actions in Power Automate with the following values:

  • Excel for Business – List rows present in a table
  • Data Operations – Select
  • Data Operations – Filter Array



Power Automate Expressions:


Select: Convert Amount To Number

float(item()?['Amount'])



Filter array: Amount GTE 500

item()?['Amount']



Output array:

TransactionDateCustomerAmount
1/3/2023Bridgewater Mill700
1/3/2023Jacks Meat Shop650
1/3/2023Green Valley Dairy890
1/4/2023Friendly Farms1000
1/5/2023Bridgewater Mill930
1/6/2023Friendly Farms710
1/6/2023Green Valley Dairy930
1/6/2023Bridgewater Mill810




Number Column Is Between Two Values

Add these actions in Power Automate with the following values:

  • Excel for Business – List rows present in a table
  • Data Operations – Select
  • Data Operations – Filter Array



Power Automate Expressions:


Select: Convert Amount To Number

float(item()?['Amount'])



Filter array: Amounts Between 300 to 700

@and(greaterOrEquals(item()?['Amount'],300),lessOrEquals(item()?['Amount'],700))



Output array:

TransactionDateCustomerAmount
1/1/2023Jacks Meat Shop310
1/3/2023Bridgewater Mill700
1/3/2023Jacks Meat Shop650
1/4/2023Bridgewater Mill330




Filter An Excel Table Date Column In Power Automate

Date columns in an Excel table cannot be filtered properly using an ODATA query because they appear as an Excel number (serial number) stored as text. The solution is to convert those columns to a number after the data has been retrieved from the file. Then we must also convert the filter date into a serial number as well. Once this is done, we can use all of the expected operators to filter – equals, does not equal, greater than, less than, greater than or equal to, less than or equal to and does not equal.




Date Column Is Equal To

Create these actions in Power Automate with the following values:

  • Excel for Business – List rows present in a table
  • Data Operations – Select
  • Data Operations – Compose
  • Data Operations – Compose
  • Data Operations – Filter Array



Power Automate Expressions


Select: Convert Transaction Date To Integer

int(item()?['TransactionDate'])


Compose: Selected Date As Excel Number

int(add(div(sub(ticks(outputs('Compose:_Selected_Date')),ticks('1900-01-01T00:00:00Z')),864000000000),1))


Filter array: Selected Date Eq 2023-01-04

item()?['TransactionDate']
outputs('Compose:_Selected_Date_As_Excel_Number')?['SelectedDate']


Output array:

TransactionDateCustomerAmount
1/4/2023Bridgewater Mill330
1/4/2023Friendly Farms1000
1/4/2023Green Valley Dairy290




Date Column Is Greater Than Or Less Than

Use these actions in Power Automate with the following values:

  • Excel for Business – List rows present in a table
  • Data Operations – Select
  • Data Operations – Compose
  • Data Operations – Compose
  • Data Operations – Filter Array



Power Automate Expressions:


Select: Convert TransactionDate To Integer

int(item()?['TransactionDate'])


Compose: Start Date As Excel Number

int(add(div(sub(ticks(outputs('Compose:_Start_Date')),ticks('1900-01-01T00:00:00Z')),864000000000),1))


Filter array: Transaction Dates GTE 2023-01-04

item()?['TransactionDate']
outputs('Compose:_Start_Date_As_Excel_Number')?['StartDate']



Output array:

TransactionDateCustomerAmount
1/4/2023Bridgewater Mill330
1/4/2023Friendly Farms1000
1/4/2023Green Valley Dairy290
1/5/2023Bridgewater Mill930
1/5/2023Jacks Meat Shop210
1/6/2023Friendly Farms710
1/6/2023Green Valley Dairy930
1/6/2023Bridgewater Mill810




Date Column Is Between Two Values

Add these actions in Power Automate with the following values:

  • Excel for Business – List rows present in a table
  • Data Operations – Select
  • Data Operations – Compose
  • Data Operations – Compose
  • Data Operations – Compose
  • Data Operations – Filter Array



Power Automate Expressions:


Select: Convert TransactionDate To Integer

int(item()?['TransactionDate'])


Composes: Dates As Excel Numbers

int(add(div(sub(ticks(outputs('Compose:_Start_Date')),ticks('1900-01-01T00:00:00Z')),864000000000),1))
int(add(div(sub(ticks(outputs('Compose:_End_Date')),ticks('1900-01-01T00:00:00Z')),864000000000),1))


Filter array: Dates Between 2023-01-02 And 2023-01-04

@and(greaterOrEquals(item()?['TransactionDate'], outputs('Compose:_Dates_As_Excel_Numbers')?['StartDate']),lessOrEquals(item()?['TransactionDate'], outputs('Compose:_Dates_As_Excel_Numbers')?['EndDate']))



Output array:

TransactionDateCustomerAmount
1/3/2023Bridgewater Mill700
1/3/2023Jacks Meat Shop650
1/3/2023Green Valley Dairy890
1/4/2023Bridgewater Mill330
1/4/2023Friendly Farms1000
1/4/2023Green Valley Dairy290





Questions?

If you have any questions or feedback about How To Filter Excel Table Rows In Power Automate: Text Numbers, Dates please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.

Matthew Devaney

Subscribe
Notify of
guest

27 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Robert Dyjas
7 months ago

In the Number Column Is Equal To can’t you filter directly? Like this:

float(item()?['Amount'])
greater or equal
500

Is the additional step for better readability?

Jerry
Jerry
7 months ago

Totally love the cat picture you attached to this!

Rich Burdes
7 months ago

Hey Matt – great work on putting this all together! As discussed – I wanted to add that as of writing this the excel connector has a couple limitations – it will only support an odata filter on 1 column in your excel file – so if you need to filter on a couple columns – you will want to use odata and then use the Power Automate ‘Filter’ action (data operations) to further refine your record set. Hope that helps anyone out there!

Also – odata filter on the excel column will only work if the excel column has no spaces in its name! Again hope that helps peeps using this connector!

Josh P
Josh P
7 months ago

Hi Matthew – this is incredibly helpful! 😀

I’m trying to filter dates between ‘now’ and ‘-15’ days, so I think this would work great however is there a way to then present the output into an email?

I managed to create a filter array that gets me the output I need but can’t see anyway of presenting that output into a send email when I do it this way.

@greaterOrEquals(item()?[‘Date of Incident’], formatDateTime(addDays(utcNow(), -15), ‘yyyy-MM-ddTHH:mm:ssZ’))

Thanks,
Josh

Terry Brisley
Terry Brisley
6 months ago

I am scratching my head at this. Great article. I came across this just at the right time. Unfortunately it hasn’t worked for me. It’s definitely me doing something wrong. I have an excel sheet with a table with headers. My column “ Diarised Date” is filled in with a date to call the customer back. When I used you method for date equal to I came across a couple of problems. I can only find compose, not compose selected date and also the compose selected date as excel number. I have tried just with compose I could find errors kept coming up. Please can you help?

Vaibhav
Vaibhav
6 months ago

Hello Matthew,

I am designing a workflow that will trigger twice a day at 6 AM and 6 PM and compile all the MS form submissions that happen between 6 AM to 6 PM and then 6 PM to 6 AM (the next day). I have two questions
1- How can I pass date and time together for the start time and the end time
2- I tried to use the filter array condition in your last case of “Date Column Is Between Two Values” and it gave me an invalid expression error.
I am curious if you can help.

Thanks,
Vaibhav

Donald Gordon
Donald Gordon
5 months ago

I am creating a Power Automate flow that filters by a Number Column Is Greater Than Or Less Than. however, I keep getting an invalid template error. So I recreated your example as i see it on the site and get the same error. Has something changed? Here is the error:

InvalidTemplate
The execution of template action ‘Filter_array’ failed: The evaluation of ‘query’ action ‘where’ expression ‘@greaterOrEquals(item()?[‘Amount’],500)’ failed: ‘The template language function ‘greaterOrEquals’ expects two parameter of matching types. The function was invoked with values of type ‘String’ and ‘Integer’ that do not match.’.

Donald Gordon
Donald Gordon
4 months ago

Hi Matthew,

Thanks for your repsonse. I have inputted the information as directed. I have placed the excel file in Teams (Sharepoint). each step inputs the same except under Select the float expression thumbnail looks different and in the Filter Array Step the item thumbnail is different than your pictures. not sure why as I am inputting the information as the same. Here is a picture of the output Select and Filter Array

Picture6.png
Donald Gordon
Donald Gordon
4 months ago
Reply to  Donald Gordon

here is the Filter Array screenshot

Picture5.png
Donald Gordon
Donald Gordon
4 months ago

Sure! Thanks for your help! Here is the peak code for select.

Picture7.png
Donald Gordon
Donald Gordon
4 months ago
Reply to  Donald Gordon

here is also the Parameter view with the expression I inputted

Picture8.png
Donald Gordon
Donald Gordon
4 months ago

Matthew,

That was it!! I didn’t realize that the editor would cause this type of problem. it seems it turned on by default in tenant. I will continue to use the classic editor. Thanks for your help!

Sandeep
Sandeep
3 months ago

Hi,
How do I filter 2 values from the same column using ‘eq’ or ‘ne’?

Gabriel
Gabriel
2 months ago

Is it possible double text condition ?

Sébastien
Sébastien
2 months ago

Hello. Is it possible to apply two different filters? I didn’t find the information yet. Thanks for your support.

Stacey T
Stacey T
1 month ago

Hi! I’m having trouble on the filter array step and receive the error, “The template language function ‘greater’ expects all of its parameters to be either integer or decimal numbers. Found invalid parameter types: ‘Null’.” I don’t think I have any nulls in my data set but even if I use an IF statement to replace nulls with zeroes, I am getting a blank output array. Any ideas?