Combiner.CombineTextByLengths

The parameters for the function are as follows:

- textColumns: A list of column names containing the text data to be combined.

- lengthRange: A list of integer values representing the minimum and maximum length of the text to be included in the combined column.

- separator (optional): A text value that will be used to separate the combined text values.

Usage

The Combiner.CombineTextByLengths function is used to combine multiple columns of text data into a single column, while filtering the text values based on their length. This function is particularly useful in situations where you have multiple columns of text data that you want to combine, but only want to include certain portions of the text.

The function takes two lists of parameters as input: a list of column names containing the text data to be combined, and a list of integer values representing the minimum and maximum length of the text to be included in the combined column.

For example, if you had three columns of text data called "First Name", "Last Name", and "Address", you could use the Combiner.CombineTextByLengths function to combine the data from these columns into a single column, while only including portions of the text that meet a specific length criteria.

Examples

Here are some examples of how to use the Combiner.CombineTextByLengths function in Power Query M:

Example 1: Basic Usage

Suppose you have a table called "Sales" with the following columns:

- Product: The name of the product sold.

- Region: The region where the product was sold.

- Sales Person: The name of the sales person who made the sale.

- Sale Amount: The amount of the sale.

You can use the Combiner.CombineTextByLengths function to combine the "Product" and "Region" columns into a single column, while only including product names that are between 5 and 10 characters in length. Here is the M code to accomplish this:

This code will create a new column called "Custom1" that contains the combined text values.

Example 2: Using a Separator

In the previous example, we used the optional separator parameter to include a comma and space between the combined text values. You can use any text value as the separator, including special characters. Here is an example using a dash as the separator:

This code will create a new column called "Custom2" that contains the combined text values separated by a dash.

Example 3: Combining Multiple Columns

You can use the Combiner.CombineTextByLengths function to combine multiple columns of text data into a single column. Here is an example combining the "Product", "Region", and "Sales Person" columns:

This code will create a new column called "Custom3" that contains the combined text values from the "Product", "Region", and "Sales Person" columns, while only including text values that are between 3 and 12 characters in length.

The Combiner.CombineTextByLengths function in Microsoft Power Query M provides a powerful tool for combining multiple columns of text data into a single column, while filtering the text based on length criteria. By understanding the syntax and usage of this function, you can take advantage of its capabilities to streamline your data analysis and reporting workflows.

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

Upcoming Courses

Contact Us