Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Dicko
Helper II
Helper II

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!

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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.

View solution in original post

5 REPLIES 5
AlexisOlson
Super User
Super User

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.

Wow Thanks it works perfectly !! Thank you mate!

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, _).

Understood, thanks for the explanation!

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.