Text.EndsWith

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Text.EndsWith

Understanding Text.EndsWith

Text.EndsWith is a simple function that takes two parameters: a text value and a text string. It returns a Boolean value, which is true if the text value ends with the specified text string, and false otherwise. Here’s an example of how to use the function:

= Text.EndsWith("Hello world", "world")

This will return true because the text value “Hello world” ends with the text string “world”.

The M Code Behind Text.EndsWith

The M code behind the Text.EndsWith function is fairly simple. Here’s the code:

(Text as nullable text, Ends as text) as logical =>

if Text.End(Text, Text.Length(Ends)) = Ends then true else false

Let’s break this down. The function takes two parameters: “Text”, which is the text value we want to check, and “Ends”, which is the text string we want to check for. The “as nullable text” part of the code means that the “Text” parameter can be null, which is useful in some cases.

The second part of the code, “if Text.End(Text, Text.Length(Ends)) = Ends then true else false”, is where the magic happens. This code checks if the specified text value ends with the specified text string. Here’s how it works:

1. The Text.End function takes two parameters: “Text” (the text value we want to check) and “Text.Length(Ends)” (the length of the text string we want to check for). This returns the last “n” characters of the text value, where “n” is the length of the specified text string.

2. The code then checks if the last “n” characters of the text value are equal to the specified text string. If they are, the function returns true. If they are not, the function returns false.

Using Text.EndsWith in Power Query

Now that we understand how the Text.EndsWith function works, let’s take a look at how to use it in Power Query. Here’s an example:

1. Open Power Query and create a new query.

2. In the “Home” tab, click on “From Other Sources” and select “Blank Query”.

3. In the “Query Editor”, click on “View” and select “Advanced Editor”.

4. In the “Advanced Editor”, enter the following code:

let

Source = #table({"Text"}, {{"Hello world"}, {"Goodbye world"}}),

Results = Table.AddColumn(Source, "EndsWithWorld", each Text.EndsWith([Text], "world"))

in

Results

This code creates a table with a single column (“Text”) and two rows (“Hello world” and “Goodbye world”). It then adds a new column (“EndsWithWorld”) that uses the Text.EndsWith function to check if each text value in the “Text” column ends with the text string “world”.

5. Click on “Done” and close the “Query Editor”.

6. The resulting table should look like this:

| Text | EndsWithWorld |

|----------------|---------------|

| Hello world | true |

| Goodbye world | true |

As you can see, the Text.EndsWith function correctly identified that both text values end with the text string “world”.

The Text.EndsWith function is a simple but powerful function in Power Query. By understanding the M code behind the function, you can use it to easily check if a given text value ends with a specified text string. This can be useful in a variety of data analysis scenarios, from cleaning up messy data to identifying patterns in large datasets.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)