Text.Split

In this article, we will explore the M code behind the Text.Split function and how it works. We will also take a look at some tips and tricks for using Text.Split in your Power Query projects.

How Text.Split Works

The Text.Split function in Power Query M takes two arguments: the text string to split and the delimiter to use for splitting. The function returns an array of substrings that were separated by the delimiter.

For example, consider the following text string: “John,Smith,32”. If we want to split this string into three substrings (i.e., “John”, “Smith”, “32”), we can use the Text.Split function with a comma delimiter as follows:

This will return the following array of substrings:

It is important to note that Text.Split is case-sensitive, which means that it will only split the string based on the exact delimiter that you specify. If you want to split a string based on multiple delimiters, you can use the Text.SplitAny function instead.

Tips and Tricks for Using Text.Split

Here are some tips and tricks for using Text.Split in your Power Query projects:

Trim Whitespace

When splitting a string, you may encounter whitespace characters (e.g., spaces, tabs, newlines) before or after the delimiter. To remove these whitespace characters, you can use the Text.Trim function in combination with Text.Split.

For example, consider the following text string: “John, Smith ,32”. If we split this string using a comma delimiter, we will get the following array of substrings:

To trim the whitespace characters from the second substring, we can use the following M code:

This will return the following substring:

Split by Length

In some cases, you may want to split a string into a fixed-length array of substrings. To do this, you can use the Text.Range function in combination with Text.Split.

For example, consider the following text string: “123456789”. If we want to split this string into an array of three substrings, each with a length of three characters, we can use the following M code:

This will return the following array of substrings:

Split by Regular Expression

If you need to split a string based on a complex pattern, you can use regular expressions in combination with the Text.Split function. Regular expressions allow you to define a pattern that matches a specific set of characters or character combinations.

For example, consider the following text string: “John|Smith|32”. If we want to split this string into an array of substrings based on the pipe “|” character, we can use the following M code:

This will return the following array of substrings:

The Text.Split function in Power Query M is a powerful tool for splitting strings into arrays of substrings based on a delimiter. By understanding the M code behind Text.Split and some tips and tricks for using it effectively, you can streamline your data transformation workflows and save time on your Power Query projects.

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

Upcoming Courses

Contact Us