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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors