Text.Contains Function in Power Query M (5 Easy Examples)

Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

Text.Contains function checks whether a piece of text contains a given substring and returns true or false.

If you want to test whether a value appears inside some text, or filter a table down to the rows that mention a word, this is the function you reach for.

Table of Contents

Syntax of Text.Contains

Text.Contains(text as nullable text, substring as text, optional comparer as nullable function) as nullable logical

Arguments

text (required, nullable text). The text value to search inside.
substring (required, text). The value to look for within text.
comparer (optional, nullable function). Controls how values are compared, for example case-insensitive or culture-aware matching. Omit it for the default case-sensitive match.

Returns: a logical value, either true or false. If text is null, it returns null.

In plain terms, you give it the text to search and the substring to look for, and it tells you whether the substring is in there.

Example 1: Check if a product name contains a word

Test whether a product name includes the word Keyboard.

Text.Contains("Wireless Keyboard", "Keyboard")

Result: true

The substring is found, so the function returns true.

Example 2: Matching is case-sensitive by default

The same check with a lowercase keyboard behaves differently.

Text.Contains("Wireless Keyboard", "keyboard")

Result: false

The default comparison is case-sensitive, so keyboard does not match Keyboard.

Example 3: Make the match case-insensitive

Pass Comparer.OrdinalIgnoreCase as the third argument to ignore case.

Text.Contains("Wireless Keyboard", "keyboard", Comparer.OrdinalIgnoreCase)

Result: true

Now the casing is ignored and the substring matches.

Example 4: Filter rows whose text contains a value

Filtering rows is the most common use of Text.Contains.

Say you have a Tickets query with a Ticket and a Subject column.

You want to keep only the rows whose subject mentions a refund.

Here is the starting data:

Ticket	Subject
T-1001	Refund request for order 5567
T-1002	Cannot log in to account
T-1003	Refund not received yet
T-1004	Change shipping address

Now wrap Text.Contains inside Table.SelectRows:

let
    Source = Excel.CurrentWorkbook(){[Name="Tickets"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Subject], "Refund"))
in
    #"Filtered Rows"

This keeps every row where Subject contains the word Refund.

The result has only the two matching rows:

Ticket	Subject
T-1001	Refund request for order 5567
T-1003	Refund not received yet

The rows about login and shipping are dropped because their subjects do not contain Refund.

Example 5: Add a true/false flag column

Instead of filtering, you can tag every row with whether it matches.

Here is the same starting data:

Ticket	Subject
T-1001	Refund request for order 5567
T-1002	Cannot log in to account
T-1003	Refund not received yet
T-1004	Change shipping address

Add an Is Refund column with Table.AddColumn, using a case-insensitive comparer:

let
    Source = Excel.CurrentWorkbook(){[Name="Tickets2"]}[Content],
    #"Added Is Refund" = Table.AddColumn(Source, "Is Refund", each Text.Contains([Subject], "refund", Comparer.OrdinalIgnoreCase), type logical)
in
    #"Added Is Refund"

This checks each subject for refund, ignoring case.

The result keeps every row and adds the new column on the right:

Ticket	Subject	Is Refund
T-1001	Refund request for order 5567	true
T-1002	Cannot log in to account	false
T-1003	Refund not received yet	true
T-1004	Change shipping address	false

Now every row carries a true or false you can filter or count later.

Things to keep in mind with Text.Contains

It matches literally, with no wildcards or regex. Searching for "a*" looks for the actual characters a*. For pattern matching, use functions like Text.Start, Text.End, or a Splitter.
null text returns null, not false. A column with blanks can hand back null, which is neither true nor false in a filter. Guard it with Text.Contains([Column] ?? "", "value") so blanks act as empty text.
Both inputs must be text. Passing a number throws Expression.Error: We cannot convert the value 42 to type Text. Convert it first with Text.From, for example Text.Contains(Text.From([Code]), "7").
Unexpected false? Check the casing. The default match is case-sensitive (see Example 2). Use Comparer.OrdinalIgnoreCase for a case-insensitive check.

Performance and query folding

Against a foldable source like SQL Server, a default Text.Contains inside Table.SelectRows can fold to a LIKE '%value%' filter, so the database does the work. Adding a comparer like Comparer.OrdinalIgnoreCase usually breaks folding, and the filter then runs locally in Power Query.

On non-foldable sources such as Excel tables, CSV files, or folders, the comparison always runs locally. That is fine for thousands of rows. On very large columns, filter as early as possible so later steps work on fewer rows.

Common questions about Text.Contains

Can I check for more than one substring at once?

Combine checks with or, for example Text.Contains([Code], "A-") or Text.Contains([Code], "7"). For a long list, use List.AnyTrue(List.Transform(myList, each Text.Contains([Code], _))).

What is the difference between Text.Contains, Text.StartsWith, and Text.EndsWith?

Text.Contains looks anywhere in the text. Text.StartsWith checks only the beginning and Text.EndsWith checks only the end.

List of All Power Query Functions

Related Power Query Functions / Articles: