Text.Proper

Understanding the Text.Proper Function

Before we dive into the M code behind Text.Proper, let’s first understand how the function works. The Text.Proper function takes a text value as input and converts it to a proper case format where the first letter of each word is capitalized, and the rest of the letters are in lowercase. For example, the input “JOHN DOE” would be converted to “John Doe”.

The function works by first converting all letters in the input text to lowercase. It then searches for spaces in the text and converts the next letter to uppercase. This process is repeated for each space found in the text.

The M Code Behind Text.Proper

The M code behind the Text.Proper function is relatively simple. It consists of two main steps:

1. Convert the input text to lowercase

2. Capitalize the first letter of each word

Let’s explore each of these steps in more detail.

Converting the Input Text to Lowercase

The first step in the Text.Proper function is to convert the input text to lowercase. This is done using the Text.Lower function. Here is the M code for this step:

In this code, “Text” is the input parameter to the Text.Proper function. The Text.Lower function is used to convert the input text to lowercase and store it in the variable “LowercaseText”. This variable is then used in the next step to capitalize the first letter of each word.

Capitalizing the First Letter of Each Word

The second step in the Text.Proper function is to capitalize the first letter of each word. This is done using the Text.Combine and Text.Capitalize function. Here is the M code for this step:

In this code, “LowercaseText” is the variable that stores the input text in lowercase format. The Text.Split function is used to split the lowercase text into separate words using a space delimiter. The List.Transform function is then used to apply the Text.Capitalize function to each word, which capitalizes the first letter of each word. Finally, the Text.Combine function is used to combine the capitalized words back into a single text value, separated by spaces.

The Text.Proper function is a powerful tool in Power Query that can be used to convert text values to a proper case format. Understanding the M code behind the function can help you to create more complex data transformations and give you a better understanding of how Power Query works. By following the steps outlined in this article, you can create your own Text.Proper function and customize it to meet your specific needs.

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

Upcoming Courses

Contact Us