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 Number 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

7 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Robert Dyjas
2 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
2 months ago

Totally love the cat picture you attached to this!

Rich Burdes
2 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
1 month 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