Splitter.SplitTextByPositions

In this article, we will explore the M code behind the Splitter.SplitTextByPositions function and how it can be used in your data transformations. We will also provide some examples to demonstrate the function’s capabilities.

Overview of the Splitter.SplitTextByPositions function

The Splitter.SplitTextByPositions function is used to split a text string into multiple columns by specifying the start and end positions of each column. This function takes two arguments:

– delimiter: The delimiter used to separate the columns. This can be any character or string.

– positions: A list of tuples that specifies the start and end positions of each column.

The function returns a table with the specified columns.

Examples

Let’s take a look at some examples of how the Splitter.SplitTextByPositions function can be used.

Example 1: Splitting a text string into multiple columns

Suppose we have a text string that contains data in the format of “name, age, city”. We want to split this text string into three separate columns for further analysis.

Here’s the M code to achieve this:

In this example, we used a comma delimiter and specified the start and end positions of each column in the positions argument. The resulting table contains three columns: name, age, and city.

Example 2: Extracting specific portions of a text string

Suppose we have a text string that contains data in the format of “product code-quantity-price”. We want to extract the quantity column and multiply it by the price column to calculate the total cost.

Here’s the M code to achieve this:

In this example, we used a hyphen delimiter and specified the start and end positions of the quantity and price columns in the positions argument. We then extracted the quantity and price values using the column names generated by the Splitter.SplitTextByPositions function. We converted these values to numbers using the Number.FromText function and calculated the total cost by multiplying the quantity and price values.

The Splitter.SplitTextByPositions function is a powerful tool for splitting text by specific positions in Power Query. By leveraging this function, you can easily extract specific portions of a text string for further analysis or manipulation in your data transformations.

In this article, we provided an overview of the function’s M code and demonstrated how it can be used in your Power Query transformations. We hope this article has been helpful in expanding your Power Query capabilities.

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

Upcoming Courses

Contact Us