Forum Discussion
Combine text from multiple columns in Power Query
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!
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.
5 Replies
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.
Hi AlexisOlson, thanks!
Do I understand correctly that (r) is a function referencing each record?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, _).
Wow Thanks it works perfectly !! Thank you mate!
