Text.ToList

One of the most useful M functions in Power Query is Text.ToList, which allows users to split a text string into a list of characters. This can be incredibly helpful when working with data that needs to be broken down into individual items, such as email addresses or product SKUs.

How to Use Text.ToList

The syntax for Text.ToList is simple:

The function takes a single argument, which is the text string that you want to split into a list. For example, if you have a column of email addresses that you want to split into individual characters, you would use the following formula:

This will create a list of characters for each email address in your data set.

Understanding the M Code

While the Text.ToList function is easy to use, understanding the M code behind it can be helpful for more advanced users who want to create more complex transformations. Here’s a breakdown of the M code for Text.ToList:

The function begins by defining the text string argument, which is marked as “nullable” to allow for null values. If the text argument is null, the function returns an empty list.

If the text argument is not null, the function calls the Text.ToList function, which splits the text string into a list of characters. The List.Transform function is then used to apply a transformation to each item in the list. In this case, the transformation is simply to return each item in the list unchanged.

Using Text.ToList in Real-World Scenarios

Now that you understand the basics of Text.ToList, let’s look at some real-world scenarios where it can be useful.

Splitting Email Addresses into Characters

As mentioned earlier, one common use case for Text.ToList is to split email addresses into individual characters. This can be helpful for analyzing email addresses in more detail, or for creating custom email validation rules.

For example, you might use the following formula to split an email address into a list of characters:

This would return a list of characters for each email address in your data set, such as:

Breaking Down Product SKUs

Another common use case for Text.ToList is to break down product SKUs into their individual components. For example, if you have a column of SKUs that contain information about the product type, color, and size, you might use Text.ToList to split the SKUs into separate lists for each component.

For example, suppose you have the following SKUs:

You could use the following formulas to split the SKUs into lists for each component:

This would result in the following lists:

Text Analysis

Finally, Text.ToList can be used for text analysis, such as counting the frequency of letters or identifying patterns in text. For example, you might use the following formula to count the number of times each letter appears in a text string:

This formula would return a table showing the frequency of each letter in the text string:

In conclusion, Text.ToList is a powerful M function in Power Query that allows users to split text strings into lists of characters. While the function is easy to use, understanding the M code behind it can help advanced users create more complex transformations. Text.ToList can be used in a variety of real-world scenarios, such as breaking down email addresses or product SKUs, or for text analysis.

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

Upcoming Courses

Contact Us