Combiner.CombineTextByRanges

Combiner.CombineTextByRanges is a function in the Power Query M language that combines a list of text into a single text using specified positions and lengths. The function returns a new function that combines the input text based on the specified ranges.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Combiner.CombineTextByRanges(
   ranges as list,
   optional template as nullable text,
) as function

Argument	Attribute	Description
Positions		The function takes a list of lists as input. Each inner list has two values: 1. The first value indicates the position where the extracted text will be placed in the output string. 2. The second value specifies the number of characters to extract from each text item in the input. A ‘null‘ value means the entire string length should be used. Note that if positions from later extractions overlap with earlier ones, the later extraction will overwrite the earlier one.
Template	Optional	The ‘template’ argument sets the initial characters for the combined output. If left unspecified or set to null, the function uses a string of spaces whose length matches the sum of the instructed lengths for extraction. If you provide a custom string, it becomes the new template. This template is then overwritten by the texts extracted during the combining process. If the template falls short of the needed length, it gets padded with extra spaces. Any residual characters from the template appear in the final output if the combined text doesn’t overwrite all characters.

Description

The Combiner.CombineTextByRanges function consolidates a list of text values into one string, using specified positions and ranges for text extraction. A template, either default or custom, is used as the initial string for the combined output.

When omitting the second argument, by default the template consists of a string of spaces. Its length is determined by the maximum value in the list of cumulative character counts. If a custom template is provided, it takes the place of the default string of spaces.

The function overwrites this template with text segments extracted according to the given positions and ranges. Should there be any leftover portions or gaps in the template, they will be kept in the combined output.

The combiner functions are a bit different from most functions in the Power Query M language because they return a function. This means that using it involves two steps, you:

specify the function parameters: the delimiter and optionally the template.
provide a list of text values that the function combines based on the parameters of step 1.

Examples

Let’s explore the Combiner.CombineTextByRanges function through a few examples.

The Basics

Imagine you have a list of text values, {“Apple”, “Grape”, “Orange}, and you wish to combine these using the Combiner.CombineTextByRanges function. With this function, you can determine both the position in the output string for each value and the number of characters to extract from each item in your list.

For demonstration, we’ll use the parameter sets { {0, 4}, {6, 2} } as follows:

Combiner.CombineTextByRanges( { {0, 4 }, {6, 2} } )

The first parameter set, {0, 4}, tells the function to take the first four characters from the first text value, and output it at the 0 position. The second parameter set, {6, 2}, indicates to extract 2 characters from the second text value, and outputting the result at the 6th position.

Applying these settings to our text values {“Apple”, “Grape”} results in the following output:

// Output: "Appl  Gr"
Combiner.CombineTextByRanges( { {0, 4 }, {6, 2} } )( {"Apple", "Grape"} )

This produces the output “Appl Gr”. Here’s how it works:

The function starts with a template of 6 spaces, which equals the total number of characters designated for extraction.
It first takes 4 characters from “Apple” and places them at the 0th position in the output string. This corresponds with the first parameter set {0, 4}.
Then it moves to “Grape,” extracts the first 2 characters, and places them at the 6th position. This is based on the second parameter set {6, 2}.

The space between “Appl” and “Gr” is a remnant from the initial 6-space template. This is because the template spaces were not overwritten by text from the list.

Using Variables for Clarity.

Combiner.CombineTextByRanges is a two-step function, which can feel a tad complex, especially if you’re accustomed to one-step Power Query M language functions. To simplify things, you can use variables like this:

// Output: "Appl  Gr"
let 
   MyFunction    = Combiner.CombineTextByRanges( { {0, 4 }, {6, 2} } ),   // returns a function
   ApplyFunction = MyFunction( {"Apple", "Grape"} ) // applies the function
 in 
   ApplyFunction

In this snippet, ApplyFunction uses MyFunction just like a regular function, hence the use of parentheses. The process is identical to the one used in the first example.

Addressing Surplus Positions

When you use the Combiner.CombineTextByRanges function, it may not always fill all the positions you’ve specified. The function starts with a ‘template’ of spaces (or a character you specify) to meet the length requirement. Any unfilled positions will retain their original template value.

For example, extracting eight characters from “Apple” starts with a template of 8 spaces. Only the first 5 characters get replaced by ‘Apple’, leaving 3 spaces from the template.

// Output: "Apple     Gr"
Combiner.CombineTextByRanges( { {0, 8 }, {9, 2} } )( 
   {"Apple", "Grape"}
)

Customizing the Template

You can specify characters other than spaces in the template. This enables you to replace the default spaces with any character you prefer.

// Output: "Apple||||Gr"
Combiner.CombineTextByRanges( { {0, 8 }, {9, 2} }, Text.Repeat("|", 11) )( 
   {"Apple", "Grape"}
)

If your template is longer than the final output, the excess characters from the template will appear at the end.

// Output: "Apple||||Gr||||"
Combiner.CombineTextByRanges( { {0, 8 }, {9, 2} }, Text.Repeat("|", 15) )( 
   {"Apple", "Grape"}
)

Overwriting Text Values

It’s important to note that later extractions can overwrite earlier ones. If you specify a starting position for a later text value that overlaps an earlier one, the later value will overwrite the former.

// Output: "Apple     Gr"
Combiner.CombineTextByRanges( { {0, 8 }, {9, 2} } )( 
   {"Apple", "Grape"}
)

// Output: "Apple Gr"
Combiner.CombineTextByRanges( { {0, 8 }, {6, 2} } )( 
   {"Apple", "Grape"}
)

Here, the first set of instructions is to create a string with 8 characters. However, the second set instructs the function to place text at position 6, which overwrites the template values that were previously there.

Dealing with Additional Text Values

If there are more text values in your list than specified ranges in the first argument, the function will exclude these extra values. For instance, consider this example where we have three 3 values but instructions for only 2:

// Output: "Apple Grape"
Combiner.CombineTextByRanges( { {0, 5 }, {6, 5} } )( 
   {"Apple", "Grape", "Orange"} 
)

Here, “Orange” is omitted because there are no instructions for its extraction.

Extracting Entire Values

In case you want to extract the full text value in a list, instead of providing a number you can provide null. This instructs the function to return the full string.

// Output: "Apple Grape"
Combiner.CombineTextByRanges( { {0, 5 }, {6, null} } )( 
   {"Apple", "Grape"}
)

While Combiner.CombineTextByRanges might seem like a niche function, knowing its existence can significantly enhance your data handling and manipulation capabilities within Power Query M. It provides a unique approach to text combination and offers specialized solutions for particular use-cases.

Used by

While you can use the Combiner.CombineTextByRanges function by itself, it also works together with:

Other functions related to Combiner.CombineTextByRanges are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/combiner-combinetextbyranges