PQ Dynamic Add Merge Column

[cr731]

In Power Query, how can I add a column which merges columns based on a list (so the list of column names gets merged)?


If done manually, I would have

let
    Source = Input_Original,
    #"Added Key" = Table.AddColumn(Source, "Key", each
        Text.Combine(
            { [Column1], [Column2], [Column3], [Column4] },
            " - "
        ))
in
    #"Added Key"

Instead, I want that list of Column1...Column4 to be a list that I feed into Text.Combine, but since it's referencing columns, it doesn't work to just provide a list

{"Column1", "Column2", "Column3", "Column4"}

I tried the following method, but it is extremely slow (the manual method loads in 2-3 seconds, this method takes minutes)

let
    Source = Input_Original,
    #"Added Key" = Table.AddColumn(Source, "Key", each
        Text.Combine(
            Record.FieldValues(Record.SelectFields(_, KeyColumns_List, MissingField.Ignore)),
            " - "
        ))
in
    #"Added Key"

There must be a better (faster) way?

 

[VBA Geek]

How are you creating KeyColumns_List ?

 

[cr731]

How are you creating KeyColumns_List ?

KeyColumns_List is an Excel table loaded to Power Query (and then converted to a list). The idea is that I want to make it easier for users to maintain the columns that should be merged to create a "Key" column (sometimes new columns need to be added to change it).

 

[VBA Geek]

when you create KeyColumns_List , try to do KeyColumns_List = List.Buffer(Your_Code_To_Load_The_List)


​does it make it faster?

 

[cr731]

when you create KeyColumns_List , try to do KeyColumns_List = List.Buffer(Your_Code_To_Load_The_List)


​does it make it faster?

Wow, that made all the difference. Same speed as the original now. Thanks!