Text.Start
Returns the first characters of a text value.
Text.Start(text as nullable text, count as number) as nullable textReturns: The first count characters, or the whole text if it is shorter, or null when the input is null.
What it does
Many codes are built in blocks. The first digit of a general ledger account often means the account type, and the first four characters of a bank reference often mean the bank. Text.Start takes the first characters of a value by count, which is the M version of LEFT. Use it when the position of the characters is fixed.
Syntax and arguments
- text: the value to read from.
- count: how many characters to return from the start.
Example
A chart of accounts uses the first digit for the account type: 1 for assets, 2 for liabilities, 3 for equity, 4 for revenue and 5 for expenses. A column for the type group is added from the account number.
let
Accounts = #table(type table [Account = text, Name = text],
{{"1000", "Cash"}, {"2100", "Accounts payable"}, {"4010", "Product sales"}}),
WithGroup = Table.AddColumn(Accounts, "Group", each Text.Start([Account], 1), type text)
in
WithGroup
The Group column is "1" for Cash, "2" for Accounts payable and "4" for Product sales. A report can then sum by group without a long list of account ranges.
Example: a second, different use
Take the first two characters of a fiscal period code such as "2026P04" to read the year, which is "20".
Things to watch
- Count is characters, not digits or words. A count larger than the text returns the whole text.
- It works on text. A number such as 4010 must be converted with Text.From first.
- For a value with a separator, Text.BeforeDelimiter is often clearer than counting positions.
- Null in gives null out.
In business terms
Account groups, cost centers and fiscal codes are often encoded by position. Taking the first characters is the quickest way to roll detail up to the level a controller reviews, such as all asset accounts or all accounts in one department.