Downloads
Power Query MTextBeginner

Text.BeforeDelimiter

Returns the part of a text value that comes before a delimiter.

Syntax
Text.BeforeDelimiter(text as nullable text, delimiter as text, optional index as any) as nullable text

Returns: The text before the first delimiter (or the chosen one), or null when the input is null.

What it does

Many finance labels are a code and a name joined by a separator: "1000-Cash", "V0042 | Acme Supply", "Dallas, TX". Text.BeforeDelimiter keeps the code and drops the rest. It finds the first occurrence of the delimiter by default, and returns the text before it.

Syntax and arguments

Example

A chart of accounts export has each account as "1000-Cash". The team needs the number on its own to join to the account master.

let
    Accounts = #table(type table [Label = text],
        {{"1000-Cash"}, {"2100-Accounts payable"}, {"4010-Product sales"}}),
    WithCode = Table.AddColumn(Accounts, "Account", each Text.BeforeDelimiter([Label], "-"), type text)
in
    WithCode

The Account column holds "1000", "2100" and "4010". Those are text, so they will join to the master list as long as the master is also text.

Example: a second, different use

Keep the vendor name from a label such as "Acme Supply Co, Dallas TX" by taking the text before the comma.

Things to watch

In business terms

Account labels, cost centers and vendor codes are often combined into one field in exports. Splitting out the code is how the chart of accounts and the vendor master join to the transactions in a clean, repeatable way.

Related functions

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