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

Question

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

I am not by a computer now, but I would try wrapping each result of the text.combine with [], i.e. "[" & Text.Combine(..., "] & [" ) & "]" — YHoffman

Olly Olly · Accepted Answer · 2018-08-28T14:00:46

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)),", "))

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

1 Answers