Text.End
Returns the last characters of a text value.
Text.End(text as nullable text, count as number) as nullable textReturns: The last count characters, or the whole text if it is shorter, or null when the input is null.
What it does
Text.End takes the last characters of a value. It is the M version of RIGHT. Finance teams use it for the last four digits of a card or bank account, for a check digit at the end of a reference, or for the suffix that marks a revision or a version.
Syntax and arguments
- text: the value to read from.
- count: how many characters to return from the end.
Example
A bank feed holds full account numbers, but the report should show only the last four digits to keep the numbers out of view.
let
Accounts = #table(type table [Bank = text, Account = text],
{{"Harbor Bank", "0045129986"}, {"Northwind Credit", "7788213"}}),
Masked = Table.AddColumn(Accounts, "Last4", each Text.End([Account], 4), type text)
in
Masked
The Last4 column is "9986" and "8213". The full number is still in the source table, so use this column for the display and keep the full value only where a match needs it.
Example: a second, different use
Take the last two characters of a document reference to read a revision code such as "-A" or "-B" that the ERP appends.
Things to watch
- Count is characters, counted from the end. A count larger than the text returns the whole text.
- The result is text, even when the characters are all digits.
- Masking a number here is for display only. Anyone who opens the query can still see the full value.
- Null in gives null out.
In business terms
Last four digits are how bank accounts, cards and checks are identified on statements and approvals. Showing only the last four keeps a report useful without spreading full account numbers around the team.