Lana B
Lana B

Reputation: 496

Power Query merge a subset of columns which name fit the criteria

Power Query 2016 standalone

I have a table with columns like this

Market  | mapped Brand  |  mapped Subbrand  |

name    |  text 1       |  text 2

I need to concatenate columns that contain a word "mapped" into a new column

I'm trying this:

  Text.Combine(  List.FindText(Table.ColumnNames(Source), "mapped")  , " ")

and get result in every row as a concat of column names

mapped Brand mapped Subbrand 

and I need a concat of their values

text 1 text 2

Upvotes: 0

Views: 318

Answers (1)

Olly
Olly

Reputation: 7891

You can create a list of the Columns which contain "mapped", then use that list to select the record fields you wish to combine

ColumnsToCombine = List.Select(Table.ColumnNames(Source), each Text.Contains(_, "mapped")),
#"Add Combined Column" = Table.AddColumn(Source, "Merged", each Text.Combine(Record.FieldValues(Record.SelectFields(_,ColumnsToCombine)),", "))

Upvotes: 2

Related Questions