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 Spreadsheet

• Filter An Excel Table Text Column In Power Automate
  ◦ Text Column Equals
  ◦ Text Column Does Not Equal
  ◦ Text Column Contains
  ◦ Text Column Starts With
  ◦ Text Column Ends With

• Filter A SharePoint Number Column In Power Apps
  ◦ Number Column Is Equal To
  ◦ Number Column Is Greater Than Or Less Than
  ◦ Number Column Is Between Two Values

• Filter A SharePoint Number Column In Power Apps
  ◦ Date Column Is Equal To
  ◦ Date Column Is Greater Than Or Less Than
  ◦ Date 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.

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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.