Text.End Function in Power Query M (4 Easy Examples)

If you want to grab the last few characters from a text value, Text.End does exactly that. Give it your text and a count, and it returns that many characters from the right end.

This works well for things like file extensions, last digits of a card or invoice number, and similar trailing bits.

Table of Contents

Syntax of Text.End Function

Text.End(text as nullable text, count as number) as nullable text

where

text (required, nullable text): The text value you want to pull characters from.
count (required, number): How many characters to take from the end of text.

Returns: A text value holding the last count characters of text. If text is null, it returns null, and if text is empty, it returns an empty string.

In plain terms, Text.End reads your text from the right side and hands back the number of characters you ask for.

Example 1: Get the last characters of a single value

Say you have an invoice ID and want just the trailing sequence number.

let
    Source = Text.End("INV-2026-0098", 4)
in
    Source

Result: 0098

The text is INV-2026-0098 and the count is 4, so you get the last four characters, 0098.

Example 2: Pull a file extension from a column

Suppose you have a table of uploaded file names and want the three-letter extension for each one.

Here is the starting data:

FileName
quarterly-report.xlsx
client-logo.png
backup-archive.zip
meeting-notes.docx

Add a column that takes the last three characters of each file name.

let
    Source = Excel.CurrentWorkbook(){[Name="Uploads"]}[Content],
    AddedExtension = Table.AddColumn(Source, "Extension", each Text.End([FileName], 3))
in
    AddedExtension

The result keeps the file name and adds the extension:

FileName	Extension
quarterly-report.xlsx	lsx
client-logo.png	png
backup-archive.zip	zip
meeting-notes.docx	ocx

Notice that quarterly-report.xlsx returns lsx, not xlsx. Text.End counts characters, so a four-letter extension needs a count of 4. To check whether a value holds a given piece of text instead, use Text.Contains.

Example 3: Mask all but the last four digits

Here you have card numbers and want to show only the final four digits, with the rest hidden.

Here is the starting data:

CardNumber
4716-8821-3390-7245
5212-6678-1140-9981
6011-3398-7752-0043

Add a column that joins a mask with the last four characters.

let
    Source = Excel.CurrentWorkbook(){[Name="Cards"]}[Content],
    Masked = Table.AddColumn(Source, "Masked", each "****-****-****-" & Text.End([CardNumber], 4))
in
    Masked

The result produces a masked card column:

CardNumber	Masked
4716-8821-3390-7245	`**--**-7245`
5212-6678-1140-9981	`**--**-9981`
6011-3398-7752-0043	`**--**-0043`

Here Text.End pulls the last four characters, and the & operator joins them onto the fixed mask text. To pad a value out to a fixed width instead, look at Text.PadStart.

Example 4: See how it handles edge cases

This example checks three tricky inputs at once: a count bigger than the text, empty text, and null.

let
    OverCount = Text.End("AB", 5),
    EmptyText = Text.End("", 3),
    NullText = Text.End(null, 3),
    Source = {OverCount, EmptyText, NullText}
in
    Source

Result: {"AB", "", null}

The count 5 is larger than "AB", so you get the whole string back instead of an error. Empty text returns empty text, and null returns null.

Things to keep in mind with Text.End

It never errors when count is too big. Ask for more characters than the text holds and Text.End clamps to the available length, returning the full value (see Example 4).
count must be a number. Pass text like "4" instead of 4 and you get Expression.Error: We cannot convert a value of type Text to type Number.
A negative count fails. Text.End expects a count of zero or more, so a negative number raises an error rather than counting from the other side.
It reads from the right. For the first characters of a value, use Text.Start, and for a slice based on markers in the text, see Text.BetweenDelimiters.

Common questions about Text.End

What is the difference between Text.End and Text.Start?

Text.End returns characters from the right side of the text, while Text.Start returns them from the left. Both take a text value and a count.

How do I get everything except the last few characters?

Text.End only returns the trailing characters, so it cannot do this on its own. Combine Text.Start with Text.Length to keep the front of the text instead.

List of All Power Query Functions

Related Power Query Functions / Articles: