Combiner.CombineTextByLengths

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

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

Syntax

Combiner.CombineTextByLengths(
   lengths as list,
   optional template as nullable text,
) as function

Argument	Attribute	Description
Lengths		A list of numerical values representing the number of characters to extract from each corresponding text value in the list. If the list of text values exceeds the lengths provided, the excess text values are ignored. If a length exceeds a text value’s character count, the entire text value is extracted without error.
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.CombineTextByLengths function consolidates a list of text values into a single string. It uses an array of lengths to specify how many characters to pull from each text item in the list. A template, either default or custom, is used as the initial structure for this combined output.

When omitting the second argument, by default the template is a string of spaces, its length determined by the sum of the list of extraction lengths. When you supply a custom template, the function uses it as the base string.

The function then overwrites this template with the characters extracted according to the lengths specified in the primary argument. If the template outlasts the extracted characters, or if positions are skipped, the remaining portions of the template are displayed in the 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 delve into some examples to better understand the function’s operation:

Basic Usage

Consider a scenario where you want to combine a list of text values, extracting a specific number of characters from each. First, you define the parameters of the Combiner.CombineTextByLengths function:

Combiner.CombineTextByLengths( { 2, 2, 6 } )

This sets up the function to extract the first two characters from the first and second list items, and the first six characters from the third list item.

Applying this function to a list of text values, such as {“Apple”, “Grape”, “Orange”}, yields:

// Output: "ApGrOrange"
Combiner.CombineTextByLengths( { 2, 2, 6 } )( {"Apple", "Grape", "Orange"} )

This operation concatenates the list of text values using the lengths provided in the specified order.

Using Variables for Clarity

The two-step approach of the Combiner.CombineTextByLengths function may initially seem complex, particularly if you’re more familiar with other Power Query M language functions. To simplify, consider using variables:

// Output: "ApGrOrange"
let 
   MyFunction    = Combiner.CombineTextByLengths( { 2, 2, 6 } ),   // returns a function
   ApplyFunction = MyFunction( {"Apple", "Grape", "Orange"} ) // applies the function
 in 
   ApplyFunction

In this case, ApplyFunction leverages MyFunction as an actual function (hence the parentheses following). It essentially mirrors what was demonstrated in the previous example, but it’s presented in a more understandable manner.

Handling Insufficient Lengths

But what happens when you have a surplus of text values compared to the lengths? The Combiner.CombineTextByLengths function deals with this adeptly by skipping the superfluous values:

// Output: "ApGr"
Combiner.CombineTextByLengths( { 2, 2 } )( 
   {"Apple", "Grape", "Orange"} 
)

Here, the function only pulls the first two characters from ‘Apple’ and ‘Grape’. Since a third length is missing, ‘Orange’ is simply bypassed. This demonstrates the function’s flexibility and robustness in handling scenarios where the number of text values exceeds the number of provided lengths.

Addressing Surplus Positions

You might be wondering what would happen if you requested a position that goes beyond a word’s character limit. In such cases, the function shows the values as instructed by the template argument. In case this argument has not been provided, by default the function shows spaces to fill in the gap.

// Output: "Apple   Gr"
Combiner.CombineTextByLengths( { 8, 2 } )( 
   {"Apple", "Grape", "Orange"} 
)

To replace these spaces with custom characters, you can provide a string for the Template argument.

// Output: "Apple***Gr"
Combiner.CombineTextByLengths( { 8, 2 }, Text.Repeat( "*", 10) )( 
   {"Apple", "Grape", "Orange"} 
)

The function always starts with the template values. It then overwrites the template with any values that are extracted during the combining operation. When the template is longer than the length of the extracted string, the function leaves the remaining characters from the template.

// Output: "Apple***Gr***"
Combiner.CombineTextByLengths( { 8, 2 }, Text.Repeat( "*", 13) )( 
   {"Apple", "Grape", "Orange"} 
)

These examples demonstrate the function’s flexibility in handling various scenarios, making it a powerful tool for niche scenarios in the M language.

Used by

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

Other functions related to Combiner.CombineTextByLengths are:

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