Table.FindText
TableReturns rows where any cell contains the given text string (case-insensitive).
Syntax
Table.FindText(table as table, text as text) as tableParameters
| Name | Type | Required | Description |
|---|---|---|---|
table | table | Yes | The table to search. |
text | text | Yes | The text string to search for across all cells. |
Return Value
table — A table containing only the rows where at least one cell contains the search text.
Remarks
Table.FindText scans every cell in the table and returns rows where at least one cell contains text as a case-insensitive substring. Non-text values (numbers, dates, logical) are converted to their text representations before comparison, so a search for "2024" will match a date column displaying 2024-01-15 as well as a text column containing "2024".
Because Table.FindText searches all columns simultaneously, it is a blunt instrument suited for exploratory work or admin-style queries rather than production logic. For targeted filtering, prefer Table.SelectRows with an explicit per-column condition — it is more readable, faster, and more likely to fold to the data source. Table.FindText does not fold.
A key gotcha: because numeric values are text-converted before matching, searching for "1" will match any number whose text representation contains a "1" (e.g., 1, 10, 21, 100). Design searches accordingly and prefer Table.SelectRows when precision matters.
Examples
Example 1: Find all orders mentioning "Widget"
let
Sales = #table(
{"OrderID", "CustomerName", "Product", "Category", "UnitPrice", "Quantity"},
{{1,"Alice","Widget A","Widgets",25.00,4},
{2,"Bob","Gadget B","Gadgets",50.00,2},
{3,"Charlie","Widget C","Widgets",15.00,10},
{6,"Bob","Thingamajig E","Misc",120.00,1}}
)
in
Table.FindText(Sales, "Widget")OrderID | CustomerName | Product | Category | UnitPrice | Quantity | |
|---|---|---|---|---|---|---|
| 1 | 1 | Alice | Widget A | Widgets | 25 | 4 |
| 2 | 3 | Charlie | Widget C | Widgets | 15 | 10 |
Example 2: Case-insensitive search matches upper and lower case
let
Employees = #table(
{"EmployeeID", "FullName", "Department"},
{{"E001","alice smith","Sales"},{"E002","BOB JONES","Engineering"},{"E003","Charlie Brown","Marketing"}}
)
in
Table.FindText(Employees, "alice")EmployeeID | FullName | Department | |
|---|---|---|---|
| 1 | E001 | alice smith | Sales |
Example 3: Numeric values are matched as text — search across all column types
let
Sales = #table(
{"OrderID", "CustomerName", "Region", "UnitPrice"},
{{1,"Alice","East",25.00},{2,"Bob","West",50.00},{6,"Bob","East",120.00}}
)
in
Table.FindText(Sales, "East")OrderID | CustomerName | Region | UnitPrice | |
|---|---|---|---|---|
| 1 | 1 | Alice | East | 25 |
| 2 | 6 | Bob | East | 120 |