Splitter.SplitTextByEachDelimiter

Introduction to Splitting Text

Splitting text is a common task in data analysis and reporting. It involves taking a string of text and dividing it into smaller pieces based on a specific character or sequence of characters called a delimiter. For example, consider the following string:

If we use the comma as the delimiter, we can split this string into separate columns for each piece of information:

Power Query makes this task easy with its Split Column feature, which allows us to split text by a delimiter and create new columns in the process.

The Splitter.SplitTextByEachDelimiter Function

Behind the scenes, Power Query uses a complex formula language called M to perform operations like splitting text. The Splitter.SplitTextByEachDelimiter function is one of the M functions that Power Query uses to split text based on multiple delimiters.

The syntax of the Splitter.SplitTextByEachDelimiter function is as follows:

Let’s break down the parameters of this function:

– `text`: This is the string of text that we want to split.

– `delimiters`: This is a list of characters or strings that we want to use as delimiters. We can specify one or more delimiters in the list.

– `quoteStyle`: This is an optional parameter that specifies the type of quoting used in the text. We can choose from four quote styles: QuoteStyle.Csv, QuoteStyle.None, QuoteStyle.Optional, or QuoteStyle.Xml.

– `extraValues`: This is an optional parameter that allows us to specify the maximum number of extra values that we want to include in the split. For example, if we set `extraValues` to 2 and there are three delimiters in the text, the function will split the text into four parts, with the last two parts combined as a single value.

Example Usage

To use the Splitter.SplitTextByEachDelimiter function in Power Query, we need to create a new query and define the M code that performs the split. Here’s an example of how we can use this function to split a list of email addresses:

1. Open a new workbook in Excel or Power BI.

2. Go to the Data tab and select Get Data > From Other Sources > Blank Query. This will open the Power Query Editor.

3. In the Power Query Editor, go to the View tab and select Advanced Editor. This will open the M code editor.

4. In the M code editor, replace the existing code with the following code:

5. Click Done to close the M code editor.

6. Click Close & Load to load the query results into a new worksheet.

In this example, we define a list of email addresses as the source data. We then use the List.Transform function to apply the Splitter.SplitTextByEachDelimiter function to each item in the list. The function splits each email address by the delimiter `; ` (semicolon followed by a space) and returns a list of lists, where each inner list contains the parts of the split email address.

The Splitter.SplitTextByEachDelimiter function is a powerful tool for splitting text in Power Query using multiple delimiters. By understanding the M code behind this function, we can create more complex splitting operations and customize the behavior to fit our specific needs. With the help of Power Query and M, we can easily manipulate and transform data to better suit our reporting and analysis requirements.

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

Upcoming Courses

Contact Us