Combiner.CombineTextByPositions

Combiner.CombineTextByPositions is a Power Query M function that returns a function that combines a list of text into a single text using the specified positions.

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

Syntax

Combiner.CombineTextByPositions(
   positions as list,
   optional template as nullable text,
) as function

Argument	Attribute	Description
Positions		A list of numerical values representing the cumulative character count to extract from a list of text values. The list should be in ascending order and can handle values greater than the text length, in which case it fills surplus characters (the gaps) with spaces. If the text list contains more values than positions, the function returns the first value with a missing position in full and disregards the rest.
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.CombineTextByPositions function merges a list of text values into a single string. It uses list of cumulative character counts to determine which portions of text to extract from each item in the list. 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 then overwrites this template with the text extracted based on the provided positions. If any gaps or unused portions of the template remain, they appear in the final 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 how to use Combiner.CombineTextByPositions with a few practical examples:

Basic Usage

Consider the task of combining {“Apple”, “Grape”, “Orange”, “Pear”} by drawing specific characters from each word. The parameters for the Combiner.CombineTextByPositions function can be set as {0, 4, 9, 12}:

Combiner.CombineTextByPositions( { 0, 4, 9, 12 } )

Applying this to our list of words, we receive:

// Output: "ApplGrapeOra"
Combiner.CombineTextByPositions( { 0, 4, 9, 12 } )( {"Apple", "Grape", "Orange"} )

This command instructs the function to draw the first four characters (4 – 0) Appl from the first item, five characters (9 – 4) Grape from the second list item, and three characters (12 – 9) Ora from the third item. After extraction, these partial strings are combined into a singular text value.

Using Variables for Clarity

The Combiner.CombineTextByPositions function’s two-step method might seem complicated, particularly for those more familiar with other Power Query M language functions. You can simplify this process by using variables:

// Output: "ApplGrapeOra"
let 
   MyFunction    = Combiner.CombineTextByPositions( { 0, 4, 9, 12 } ),   // returns a function
   ApplyFunction = MyFunction( {"Apple", "Grape", "Orange"} ) // applies the function
 in 
   ApplyFunction

Here, ApplyFunction utilizes MyFunction as a genuine function, hence the use of opening and closing parentheses. This procedure is identical to the earlier example.

Managing Excess Text Values

If you have more text values than positions, the Combiner.CombineTextByPositions function handles this elegantly. It delivers the full length of the first value where a position is missing and omits the remaining values.

The following example returns the first four characters (4 – 0) Appl of the first word, and all characters of the second word, as there is no specified position for it. The third and fourth words are omitted.

// Output: "ApplGrape"
Combiner.CombineTextByPositions( { 0, 4 } )( 
   {"Apple", "Grape", "Orange", "Pear"}
)

The following example returns the first four characters (4 – 0 ) Appl of the first word, one character (5 – 4 ) G of the second word and all characters of the third word because no position is specified. The fourth word Pear is not returned.

// Output: "ApplGOrange"
Combiner.CombineTextByPositions( { 0, 4, 5 } )( 
   {"Apple", "Grape", "Orange", "Pear"}
)

Addressing Surplus Positions

When you ask the function to extract more characters than are present in your input string, it still starts with a ‘template’. This template is a, by default, a string of spaces. The function then overwrites these spaces with the characters you extract from your input. If the input values are shorter than the instructed length you wish to extract, the remaining spaces from the template will appear in the output.

// Output: "Apple     Grape"
Combiner.CombineTextByPositions( { 0, 10 } )( 
   {"Apple", "Grape"}
)

You can replace the default template of spaces with your own set of characters. This way, the output shows these custom characters instead of spaces.

// Output: "Apple*****Grape"
Combiner.CombineTextByPositions( { 0, 10 }, "***************")( 
   {"Apple", "Grape"}
)

// Output: "Apple*****Grape"
Combiner.CombineTextByPositions( { 0, 10 }, Text.Repeat("*", 15 ))( 
   {"Apple", "Grape"}
)

When the template’s length exceeds the combined length of your extracted characters, additional filler characters will appear at the end of the output.

// Output: "Apple*****Grape****"
Combiner.CombineTextByPositions( { 0, 10 }, Text.Repeat("*", 19 ))( 
   {"Apple", "Grape"}
)

These examples showed how the Combiner.CombineTextByPositions function is useful for combining texts. It’s certainly a niche function but knowing its basics may help you in special use-cases for handling and manipulating your data in Power Query M.

Used by

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

Other functions related to Combiner.CombineTextByPositions are:

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