Text.Reverse

In this article, we will explore the M code behind the Text.Reverse function and learn how it can be used to manipulate text in Power Query.

Understanding the M Language

Before we dive into the M code behind Text.Reverse, let’s first understand what the M language is. M is the formula language used in Power Query, which is a functional, case-sensitive, and highly expressive language that supports a wide range of data manipulation operations.

M code is created by using the Power Query Editor, which provides an intuitive interface for creating custom queries and transformations. M code is generated automatically as we create queries in the Editor, and we can also edit the code directly to create more complex transformations.

Power Query is designed to be a user-friendly tool that does not require extensive knowledge of coding, but understanding the M language can be helpful in creating more efficient and customized queries.

The Text.Reverse Function

The Text.Reverse function is a built-in function in Power Query that allows us to reverse the order of characters in a given text string. The syntax for this function is as follows:

The function takes a text value as input and returns the same text value with the order of characters reversed. If the input value is null, the function returns null.

Let’s take a look at an example to understand how the Text.Reverse function works:

The output of this function would be:

As we can see, the order of characters in the original text string has been reversed.

The M Code Behind Text.Reverse

Now that we understand how the Text.Reverse function works, let’s take a look at the M code behind it. When we use the Text.Reverse function in Power Query, the following M code is generated:

This code defines a lambda function that takes a text value as input and returns the same text value with the order of characters reversed. The lambda function is then applied to the relevant column or data source in the query.

Let’s break down this code further:

– The first part of the code `(text) =>` defines the input parameter for the lambda function. In this case, the input parameter is named `text`.

– The second part of the code `Text.Reverse(text)` defines the operation to be performed on the input parameter. In this case, the Text.Reverse function is applied to the `text` parameter.

Using Text.Reverse in Power Query

Now that we understand the M code behind the Text.Reverse function, let’s see how we can use it in Power Query.

Suppose we have a data source that contains a list of names that are not formatted consistently. Some names are in uppercase, some are in lowercase, and some have extra spaces or punctuation marks. We want to clean up this data and format all names in uppercase.

To achieve this, we can create a new query in Power Query and follow these steps:

1. Import the data source into the query.

2. Select the column containing the names and click on the Transform tab.

3. Click on the Replace Values button and replace any spaces or punctuation marks with an empty string.

4. Click on the Add Column tab and select the Custom Column option.

5. In the Custom Column dialog box, enter a name for the new column and enter the following M code:

6. Click OK to create the new column.

This M code defines a lambda function that takes a text value as input, applies the Text.Reverse function to it to reverse the order of characters, and then applies the Text.Upper function to convert the text to uppercase.

Once the new column is created, we can remove the original column containing the unformatted names and rename the new column as required.

The Text.Reverse function is a powerful tool in Power Query that allows us to easily manipulate text values. Understanding the M code behind this function can be helpful in creating customized queries and transformations.

By using the Text.Reverse function in combination with other string manipulation functions in Power Query, we can quickly and efficiently clean up and format data, making it easier to analyze and visualize.

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

Upcoming Courses

Contact Us