PQ Dynamic Add Merge Column

cr731

Well-known Member
Joined
Sep 17, 2010
Messages
611
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


Code:
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


Code:
{"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)


Code:
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?
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

cr731

Well-known Member
Joined
Sep 17, 2010
Messages
611
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

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
when you create KeyColumns_List , try to do KeyColumns_List = List.Buffer(Your_Code_To_Load_The_List)


​does it make it faster?
 

cr731

Well-known Member
Joined
Sep 17, 2010
Messages
611
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,139
Messages
5,527,055
Members
409,741
Latest member
serfr

This Week's Hot Topics

Top