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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors