Text.PositionOfAny

One of the many functions available in the M language is Text.PositionOfAny. This function is used to find the first occurrence of any character from a given set of characters in a text string. In this article, we’ll explore the M code that powers this function and how it can be used to transform data in Power Query.

Understanding the Text.PositionOfAny Function

Before diving into the M code behind the Text.PositionOfAny function, it’s important to understand its basic syntax and functionality. The function takes two arguments: the first is the text string to be searched, and the second is a list of characters to search for.

For example, if we wanted to find the position of the first occurrence of either “a” or “b” in the text string “abcde”, we would use the following formula:

This would return the value 1, since “a” is the first character in the list of search characters and appears at position 1 in the text string.

The M Code Behind Text.PositionOfAny

The M code behind the Text.PositionOfAny function is relatively simple. Here’s the basic structure of the function:

Let’s break down each line of the code to understand how it works.

Line 1: Function Arguments

The first line of the code defines the function arguments. The “Text” argument is defined as a text string, and the “List” argument is defined as a list of characters to search for.

Line 2: Variable Declaration

The “let” keyword signals the start of a variable declaration block. In this case, we’re declaring a variable called “Positions”, which will be used to store the position of each search character in the text string.

The List.Transform function is used to iterate over each character in the search list and apply the Text.PositionOf function to the text string. The result is a list of position values.

Line 3: Finding the First Position

The “FirstPosition” variable is defined as the minimum value in the “Positions” list. This is achieved using the List.Min function.

Line 4: Return Value

Finally, the function returns the value of “FirstPosition” + 1, unless “FirstPosition” is null. If “FirstPosition” is null, this means that none of the search characters were found in the text string, and the function returns null.

Using Text.PositionOfAny in Power Query

The Text.PositionOfAny function can be used in a wide range of data transformation scenarios in Power Query. Here are some examples of how it can be used:

Example 1: Extracting Substrings Based on Search Characters

Suppose we have a table of product names, and we want to extract the first word from each name. We can use the Text.PositionOfAny function to find the position of the first space character in each name, and then extract the substring up to that position. Here’s the M code to achieve this:

The resulting table would look like this:

| First Word |

|————|

| Red |

| Green |

| Yellow |

Example 2: Replacing Characters Based on Search Characters

Suppose we have a table of email addresses, and we want to replace all periods (“.”) with underscores (“_”) in the local part of each address. We can use the Text.PositionOfAny function to find the position of the first occurrence of a period, and then replace it with an underscore using the Text.ReplaceRange function. Here’s the M code to achieve this:

The resulting table would look like this:

| Updated Email |

|————————|

| john_doe@example.com |

| jane_smith@example.com |

| jack_brown@example.com |

The Text.PositionOfAny function is a powerful tool for data transformation in Power Query. By understanding the M code behind this function, we can use it to extract, transform, and load data from a wide range of sources with ease. Whether we’re extracting substrings, replacing characters, or performing other types of transformations, the Text.PositionOfAny function is a valuable addition to our Power Query toolbox.

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

Upcoming Courses

Contact Us