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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

Top Solution Authors
Top Kudoed Authors