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

When you want to check if a piece of text finishes with a specific ending, the Text.EndsWith function gives you a true or false answer. It comes in handy for jobs like keeping only the rows whose file names end in .csv or .pdf.

Table of Contents

Syntax of Text.EndsWith Function

Text.EndsWith(text as nullable text, substring as text, optional comparer as nullable function) as nullable logical

where:

text (required, nullable text): The text value you want to check.
substring (required, text): The ending you want to look for at the end of text.
comparer (optional, nullable function): A Comparer function that controls how the match is made, such as case sensitivity. If you leave it out, the match is case-sensitive.

Returns: A logical value. It is true when text ends with substring, and false when it does not. If text is null, the result is null.

In plain terms, it answers one question: does this text end with that ending?

Example 1: Check if a file name ends with .pdf

Here you check whether the file name invoice_final.pdf ends with .pdf.

let
    Source = Text.EndsWith("invoice_final.pdf",".pdf")
in
    Source

Result: true

The text finishes with .pdf, so the function returns true.

Example 2: See how the default match is case-sensitive

Here you check whether REPORT.CSV ends with .csv using the default settings.

let
    Source = Text.EndsWith("REPORT.CSV",".csv")
in
    Source

Result: false

By default the match is case-sensitive. The text ends with .CSV in uppercase, which does not match the lowercase .csv, so the result is false.

Example 3: Ignore case with a comparer

Here you check the same REPORT.CSV text against .csv, but you pass Comparer.OrdinalIgnoreCase to ignore case.

let
    Source = Text.EndsWith("REPORT.CSV",".csv",Comparer.OrdinalIgnoreCase)
in
    Source

Result: true

With Comparer.OrdinalIgnoreCase, uppercase and lowercase letters count as the same. Now .CSV matches .csv, so the result is true.

Example 4: Filter a table to keep rows ending with .csv

Say you have a table named Example4 with a list of file names.

FileName
report.csv
Summary.CSV
data.csv
notes.txt
export.csv

You want to keep only the rows whose FileName ends with .csv. Use Text.EndsWith inside Table.SelectRows.

let
    Source = Excel.CurrentWorkbook(){[Name="Example4"]}[Content],
    KeepCsv = Table.SelectRows(Source,each Text.EndsWith([FileName],".csv"))
in
    KeepCsv

Result:

FileName
report.csv
data.csv
export.csv

The default match is case-sensitive. Summary.CSV ends with .CSV in uppercase, so it is dropped along with notes.txt. Only the three lowercase .csv rows are kept.

Things to keep in mind with Text.EndsWith

The match is case-sensitive by default. To ignore case, pass Comparer.OrdinalIgnoreCase as the third argument, as shown in Example 3.
If text is null, the function returns null, not false. Plan for this when your column has blank values.
Both text and substring must be text values. Passing a number throws Expression.Error: We cannot convert the value ... to type Text.. Wrap numbers with Text.From first.
The match is a literal one. Text.EndsWith looks for the exact substring at the end and does not support wildcards or regular expressions.

Common questions about Text.EndsWith

Text.EndsWith vs Text.Contains vs Text.StartsWith?

Text.EndsWith checks the end of the text, Text.StartsWith checks the beginning, and Text.Contains checks anywhere inside the text.

How do I do a case-insensitive ends-with check?

Pass Comparer.OrdinalIgnoreCase as the third argument. Then REPORT.CSV will match .csv, as shown in Example 3.

List of All Power Query Functions

Related Power Query Functions / Articles: