Combine text from multiple columns in Power Query

[Dicko]

Here's a table with the results I seek:

col_1	col_2	result
ape	bee	ape,bee
house	street	house,street

Now I have something like this (see below), where I collect each column that starts with "col_" (for up front I don't know how many columns there are) and use that information ('MyColumns') to create a custom column "result":

...

MyColumns = List.Buffer(List.Select(Table.ColumnNames(#"PreviousStep"), each Text.StartsWith(_, "col_"))),
#"Merged Columns" = Table.AddColumn(#"PreviousStep", "result", each Text.Combine(MyColumns, ";"), type text)

This results in "col_1,col_2", which is not what I need 😞

I hope someone can help. Thanks!

[AlexisOlson · Accepted solution]

Try this as the next step:

= Table.AddColumn(#"Merged Columns", "Custom",
    (r) => Text.Combine(List.Transform(MyColumns, each Record.Field(r, _)), ","),
    type text)

This turns the list of columns into the values in those columns and concatenates the list with Text.Combine.

[SebSchoon1]

Wow Thanks it works perfectly !! Thank you mate!

[Dicko]

Hi @AlexisOlson, thanks!
Do I understand correctly that (r) is a function referencing each record?

[AlexisOlson]

Yes. There's nothing special about the letter "r" but I needed to give it a name so that I could distinguish between the list environment and the row (record) environment when writing Record.Field(r, _).

[Dicko]

Understood, thanks for the explanation!