What is Table.FindText in Power Query and How Do We Use It?

Table.FindText is a Power Query function in Power BI that returns the rows in a table that contain specific text. If the text is not found in the table, then an empty table is returned.

Syntax

Table.FindText has simple syntax and can be written as the below.

Table.FindText(table as table, text as text) as table

How Do We Use Table.FindText

In Power BI, open the Power Query window.

Select a dataset and click on the ‘fx’ button left of the query textbox.

Delete everything that appears in the textbox and paste the below calculation in, and press enter. You should now see the table filter down to just show the details for ‘Jeff’.

= Table.FindText(
    Table.FromRecords({
        [ID = 1, name="Bob", country="Australia"],
        [ID = 2, name="Bill", country="France"],
        [ID = 3, name="Jeff", country="Canada"]
    }),
    "Jeff"
)

The above calculation works by finding the text ‘Jeff’ from the custom table we created using the Table.FromRecords function. Since Jeff exists in the table, it shows only Jeff.

By using the text ‘Jeff’ we are searching the ‘name’ column, but what if we what to search by country? Instead of ‘Jeff’ we can enter a country.

As you can see, by changing ‘Jeff’ to ‘Australia’ we were able to get a result for Bob.

However, did you have notice that the ‘ID’ column is a number? If we wanted to search by the ID we would get an error because this column isn’t text.

To fix this, we can simply change the column type to text rather than a number.

You can now see the returned result is based off the ID.

Finishing Off

We have explained what the Table.FindText function is in Power Query, the syntax, and how to use this.

Table.FindText is an extremely useful function that allows you to find text in a dataset and return only the rows in the form of a table.

To learn more, please visit Learn DAX