Downloads
Power Query MTextBeginner

Text.StartsWith

Tests whether a text value begins with a given piece of text.

Syntax
Text.StartsWith(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.StartsWith checks the beginning of a value. Many finance codes are classified by a prefix: credit memos that start with "CR", intercompany accounts that start with "IC-", and voucher numbers that start with a year. This test is case sensitive unless you pass Comparer.OrdinalIgnoreCase.

Syntax and arguments

Example

An accounts payable team wants to separate vendor credit memos from invoices in a single extract, using the prefix the ERP puts on each credit.

let
    Docs = #table(type table [Reference = text, Amount = number],
        {{"CR-2207", -350}, {"INV-88341", 1200}, {"cr-2208", -75}}),
    Typed = Table.AddColumn(Docs, "IsCredit",
        each Text.StartsWith([Reference], "CR-", Comparer.OrdinalIgnoreCase), type logical)
in
    Typed

The first and third rows are flagged true, so the two credit memos are found even though one has a lower case prefix. The invoice is false.

Example: a second, different use

Check that every intercompany account code begins with "IC" before a consolidation run, and list the exceptions.

Things to watch

In business terms

Document prefixes tell you what a record is: an invoice, a credit, a payment or an adjustment. A reliable prefix test is the first control in a credit memo report or an intercompany check.

Related functions

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