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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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