Splitter.SplitTextByRanges

In this article, we will explore the M code behind the SplitTextByRanges function and how it can be used to extract and transform data.

What is the SplitTextByRanges Function?

The SplitTextByRanges function in Power Query is used to split a given text string into separate columns based on the specified ranges. This function takes two arguments – the text string to be split and a list of ranges that define the start and end positions of each column.

For example, consider the following text string: “John,Smith,30,New York”. We can use the SplitTextByRanges function to split this text into four separate columns – First Name, Last Name, Age, and City – using the following ranges:

– First Name: 0-3

– Last Name: 5-9

– Age: 10-11

– City: 12-19

Using these ranges, the SplitTextByRanges function will split the text string into four columns, as shown below:

| First Name | Last Name | Age | City |

|————|———–|—–|———–|

| John | Smith | 30 | New York |

The M Code Behind the SplitTextByRanges Function

To understand the M code behind the SplitTextByRanges function, we first need to understand how ranges are defined in Power Query. A range is defined as a record with two fields – Start and Length. The Start field specifies the starting position of the range, and the Length field specifies the length of the range.

For example, the range {Start: 0, Length: 3} defines a range that starts at position 0 and has a length of 3.

The SplitTextByRanges function uses the List.Transform function to split the text string into separate columns based on the specified ranges. The List.Transform function applies a given function to each element in a list and returns a new list with the results.

In the case of the SplitTextByRanges function, the function applied to each range is the Text.Range function, which extracts a substring from a given text string based on the specified range.

The M code for the SplitTextByRanges function is shown below:

The SplitTextByRanges function takes two arguments – the text string to be split and a list of ranges. Inside the function, the List.Transform function is used to apply the Text.Range function to each range in the list.

The result of the List.Transform function is a list of substrings, which is then transformed into a record using the Record.FromList function. The Record.FromList function creates a new record with fields corresponding to the elements in the list.

Using the SplitTextByRanges Function

Now that we understand the M code behind the SplitTextByRanges function, let’s take a look at how it can be used to extract and transform data.

Suppose we have a dataset containing customer information in the following format: “John Smith,30,New York”. We can use the SplitTextByRanges function to split this text into separate columns for First Name, Last Name, Age, and City.

To do this, first, we need to define the ranges for each column as a list of records, as shown below:

In this example, we define the ranges for each column as a list of records and pass it to the SplitTextByRanges function along with the text string.

The result of this query is a new table with four columns – First Name, Last Name, Age, and City – containing the extracted data, as shown below:

| First Name | Last Name | Age | City |

|————|———–|—–|———–|

| John | Smith | 30 | New York |

The SplitTextByRanges function in Power Query is a powerful tool for extracting and transforming data. By using predefined ranges, users can split text into separate columns with ease and efficiency.

In this article, we explored the M code behind the SplitTextByRanges function and demonstrated how it can be used to extract and transform data in Power Query. We hope this article has been helpful in understanding the power and versatility of Power Query.

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

Upcoming Courses

Contact Us