Downloads
Power Query MTextBeginner

Text.Contains

Tests whether a text value contains a given piece of text.

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

Returns: true or false, or null when the input is null.

What it does

Text.Contains answers the question "does this description include this word?" It is how a team flags transactions for review by keyword, such as every line that mentions "bonus" or "accrual". The test is case sensitive by default, so "Accrual" and "accrual" are different. Pass Comparer.OrdinalIgnoreCase as the third argument to ignore case.

Syntax and arguments

Example

A controller wants every journal line whose description mentions an accrual, whatever the capitals. The third argument makes the search ignore case.

let
    Lines = #table(type table [Description = text],
        {{"Accrual reversal Q3"}, {"accrual for legal fees"}, {"Payroll tax"}}),
    Flagged = Table.AddColumn(Lines, "IsAccrual",
        each Text.Contains([Description], "accrual", Comparer.OrdinalIgnoreCase), type logical)
in
    Flagged

The first two rows are flagged true and the payroll row is false. Without Comparer.OrdinalIgnoreCase, "Accrual reversal Q3" would be missed because of its capital A.

Example: a second, different use

Flag bank memos that mention a returned payment, using a short list of keywords combined with Text.Contains inside a custom column.

Things to watch

In business terms

Keyword flags drive a lot of month-end review: accruals, reversals, manual journals, intercompany and one-time items. A search that ignores case catches what the team typed by hand, which is usually the messy part of the ledger.

Related functions

All Power Query M functions · View as Markdown · M basics