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

Imported reference from Matthew Devaney's blog for learning purposes. Original: https://www.matthewdevaney.com/how-to-filter-excel-table-rows-in-power-automate-text-numbers-dates/. Author: Matthew Devaney.

How To Filter Excel Table Rows In Power Automate: Text Numbers, Dates Posted by - Matthew Devaney on - October 1, 2023 35 Comments

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 Date 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. TransactionDate Customer Amount 1/1/2023 Jacks Meat Shop 310 1/3/2023 Bridgewater Mill 700 1/3/2023 Jacks Meat Shop 650 1/3/2023 Green Valley Dairy 890 1/4/2023 Bridgewater Mill 330 1/4/2023 Friendly Farms 1000 1/4/2023 Green Valley Dairy 290 1/5/2023 Bridgewater Mill 930 1/5/2023 Jacks Meat Shop 210 1/6/2023 Friendly Farms 710 1/6/2023 Green Valley Dairy 930 1/6/2023 Friendly Farms 810

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' Code language: JavaScript ( javascript ) Output array: TransactionDate Customer Amount 1/4/2023 Friendly Farms 1000 1/6/2023 Friendly Farms 710 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' Code language: JavaScript ( javascript ) Output Array: TransactionDate Customer Amount 1/1/2023 Jacks Meat Shop 310 1/3/2023 Bridgewater Mill 700 1/3/2023 Jacks Meat Shop 650 1/3/2023 Green Valley Dairy 890 1/4/2023 Bridgewater Mill 330 1/4/2023 Green Valley Dairy 290 1/5/2023 Bridgewater Mill 930 1/5/2023 Jacks Meat Shop 210 1/6/2023 Green Valley Dairy 930 1/6/2023 Bridgewater Mill 810 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' Code language: JavaScript ( javascript ) Output Array: TransactionDate Customer Amount 1/1/2023 Jacks Meat Shop 310 1/3/2023 Jacks Meat Shop 650 1/5/2023 Jacks Meat Shop 210 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' Code language: JavaScript ( javascript ) Output Array: TransactionDate Customer Amount 1/3/2023 Bridgewater Mill 700 1/4/2023 Bridgewater Mill 330 1/5/2023 Bridgewater Mill 930 1/6/2023 Bridgewater Mill 810 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' Code language: JavaScript ( javascript ) Output Array: TransactionDate Customer Amount 1/3/2023 Green Valley Dairy 890 1/4/2023 Green Valley Dairy 290 1/6/2023 Green Valley Dairy 930

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' ] ) Code language: CSS ( css ) Filter array: Amount Eq 1000 item ()? [ 'Amount' ] Code language: CSS ( css ) Output array: TransactionDate Customer Amount 1/4/2023 Friendly Farms 1000

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' ] ) Code language: CSS ( css ) Filter array: Amount GTE 500 item ()? [ 'Amount' ] Code language: CSS ( css ) Output array: TransactionDate Customer Amount 1/3/2023 Bridgewater Mill 700 1/3/2023 Jacks Meat Shop 650 1/3/2023 Green Valley Dairy 890 1/4/2023 Friendly Farms 1000 1/5/2023 Bridgewater Mill 930 1/6/2023 Friendly Farms 710 1/6/2023 Green Valley Dairy 930 1/6/2023 Bridgewater Mill 810 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' ] ) Code language: CSS ( css )

Filter array: Amounts Between 300 to 700

@and(greaterOrEquals(item()?[ 'Amount' ], 300 ),lessOrEquals(item()?[ 'Amount' ], 700 )) Code language: JavaScript ( javascript ) Output array: TransactionDate Customer Amount 1/1/2023 Jacks Meat Shop 310 1/3/2023 Bridgewater Mill 700 1/3/2023 Jacks Meat Shop 650 1/4/2023 Bridgewater Mill 330

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' ] ) Code language: CSS ( css ) Compose: Selected Date As Excel Number

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

Filter array: Selected Date Eq 2023-01-04 item ()? [ 'TransactionDate' ] Code language: CSS ( css )

outputs( 'Compose:_Selected_Date_As_Excel_Number' )?[ 'SelectedDate' ] Code language: JavaScript ( javascript ) Output array: TransactionDate Customer Amount 1/4/2023 Bridgewater Mill 330 1/4/2023 Friendly Farms 1000 1/4/2023 Green Valley Dairy 290

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' ] ) Code language: CSS ( css ) Compose: Start Date As Excel Number

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

Filter array: Transaction Dates GTE 2023-01-04 item ()? [ 'TransactionDate' ] Code language: CSS ( css )

outputs( 'Compose:_Start_Date_As_Excel_Number' )?[ 'StartDate' ] Code language: JavaScript ( javascript ) Output array: TransactionDate Customer Amount 1/4/2023 Bridgewater Mill 330 1/4/2023 Friendly Farms 1000 1/4/2023 Green Valley Dairy 290 1/5/2023 Bridgewater Mill 930 1/5/2023 Jacks Meat Shop 210 1/6/2023 Friendly Farms 710 1/6/2023 Green Valley Dairy 930 1/6/2023 Bridgewater Mill 810 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' ] ) Code language: CSS ( css ) Composes: Dates As Excel Numbers

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

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

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' ])) Code language: JavaScript ( javascript ) Output array: TransactionDate Customer Amount 1/3/2023 Bridgewater Mill 700 1/3/2023 Jacks Meat Shop 650 1/3/2023 Green Valley Dairy 890 1/4/2023 Bridgewater Mill 330 1/4/2023 Friendly Farms 1000 1/4/2023 Green Valley Dairy 290 Did You Enjoy This Article? 😺

Subscribe to get new Copilot Studio articles sent to your inbox each week for FREE Enter your email address Sign Me Up 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 Power Automate

Save Docusign Document To SharePoint With Power Automate