Combiner functions in Power Query
In Power Query "Table.CombineColumns" function is used to combine values from several columns (1) into one column (2). This function takes 4 arguments. First argument is source table. Second argument is list of columns to combine. Last argument is name of newly created column.
Table.CombineColumns( Source, { "C1","C2","C3","C4" }, Combiner.CombineTextByDelimiter("<>"), "CombineByDelimiter" )
Third argument is what interests us. That argument explains how to combine column values. That argument is given by using one of this five functions:
- Combiner.CombineTextByDelimiter
- Combiner.CombineTextByEachDelimiter
- Combiner.CombineTextByLengths
- Combiner.CombineTextByPositions
- Combiner.CombineTextByRanges
Some of these functions are simple, but others are not so obvious.
Combiner.CombineTextByDelimiter
This version is simple. It concatenate all values by placing delimiter between them. Combiner. CombineTextByDelimiter function is used just to give that delimiter. In thise example, delimiter is "–".
Table.CombineColumns( Source, { "C1","C2","C3","C4" }, Combiner.CombineTextByDelimiter("-"), " CombinedByDelimiter" )| C1 | C2 | C3 | C4 | CombinedByDelimiter | |
| A | BBBBB | C | DDDD | => | A–BBBB–C–DDDD |
This is similar to TextJoin function in Excel:
| TextJoin | ||
| =TEXTJOIN(" – ";TRUE;A2:D2) | => | A–BBBB–C–DDDD |
Combiner.CombineTextByDelimiter can have one more argument. That argument could take value QuoteStyle.None or QuoteStyle.Csv. Quote.Csv will change every column which has quotes, so that column content will be delimited with quotes and all original quotes will be doubled.
| A | BB"BB | C | DDDD | => | A–BB"BB–C–DDDD | QuoteStyle.None |
| A | BB"BB | C | DDDD | => | A–"BB""BB"–C–DDDD | QuoteStyle.Csv |
Combiner.CombineTextByDelimiter( "-", QuoteStyle.Csv or QuoteStyle.None)Combiner.CombineTextByEachDelimiter
This version is similar to previous. This time we give a list of delimiters. Those delimiters are used sequentially to separate column values.
Table.CombineColumns( GiveType, { "C1","C2","C3","C4" }, Combiner.CombineTextByEachDelimiter( { "_1_", "_2_", "_3_" }, QuoteStyle.Csv), "CombineByDelimiter" )| C1 | C2 | C3 | C4 | CombinedByEachDelimiter | |
| A | BBBB | C | DDDD | => | A_1_BBBB_2_C_3_DDDD |
The question is what if have too many or too less of delimiters. If we have five delimiters, but four columns, the result will be unchanged "A_1_BBBB 2_C_3_DDDD". Surplus delimiters will not be used. If we have only two delimiters, but four columns, the result be "A_1_BBBB_2_CDDDD". There will be no delimiters between "C" and "DDDD".
This function also accepts QuoteStyle second argument.
Combiner.CombineTextByLengths
We define list of numbers. From each column only so many characters will be presented.
Table.CombineColumns( GiveType, { "C1","C2","C3","C4" }, Combiner.CombineTextByLengths( { 2, 3, 3, 3 } ), "CombinedByLenghts" )From first column we will take only two characters, from second column only three characters and so on. If we don't have enough characters in our column, column content will be padded with spaces to reach reserved space. If we have too many characters in column, rest of characters will be trimed. If our argument is { 2, 3, 3 } then the last column will not be presented.
| C1 | C2 | C3 | C4 | CombinedByLenghts | |
| A | BBBB | C | DDDD | => | A␣BBBC␣␣DDD |
This function has another argument called "template". Template is "background" text that we can use to create mask for our text.
Table.CombineColumns( GiveType, { "C1","C2","C3","C4" }, Combiner.CombineTextByLengths( { 2, 3, 3, 6 }, "*******" ), "CombineByDelimiter" )If our "background" mask is "*******" then this will combine with previous result "A␣BBBC␣␣DDD" to create "A*BBBC*␣DDD".
Combiner.CombineTextByPositions
In this case, start of every column content is determined by positions list.
Table.CombineColumns( GiveType, { "C1","C2","C3","C4" }, Combiner.CombineTextByPositions( { 2, 4, 6, 9, 12 } ), "CombineByPositions" )So, content of first column will start on position (2), of second column on position (4), third column on position (6), last column on position (9). If our list contains only numbers { 2, 4, 6 } then there will be no last column values in final result.
| 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
| A… | B… | C… | D… |
Then, we'll add rest of letters to see what is happening. Columns will fill as much space as possible. If column content is not enough, reserved space will be filled with spaces. If our list only has values { 2, 4, 6, 9 }, last column would not be trimed to three "DDD".
| 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
| A | B | B | C | D | D | D |
This is final result.
| C1 | C2 | C3 | C4 | CombinedByPositions | |
| A | BBBB | C | DDDD | => | ␣␣A␣BBC␣␣DDD |
This function also accepts Template second argument.
Combiner.CombineTextByRanges
This version is combination of Positions and Lengths versions. Our argument is sequence of pairs. First value in pair is position. The second is length.
Table.CombineColumns( GiveType, { "C1","C2","C3","C4" }, Combiner.CombineTextByRanges( { { 2,2 },{ 4, 4},{ 5,2 },{ 7,3 } } ), "CombineByPositions" )We can see bellow that content of every column starts on positions 2, 4, 5, 7 respectively. Each columns content has maximal length 2, 4, 2, 3 characters respectively. Latter columns will overwrite former columns. For example, column 3 will overwrite column 2 on position 5 ("C" instead of "B").
| 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | |
| C1 | A | ␣ | ||||||||
| C2 | B | B | B | B | ||||||
| C3 | C | ␣ | ||||||||
| C4 | D | D | D | |||||||
| result | A | B | C | B | D | D | D |
If we only use pairs { { 2,2 },{ 4, 4},{ 5,2 } }, then last column will not be presented. Length of range can not be zero.
This function also accepts Template second argument.
Excel sample file:
