Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: PQ Dynamic Add Merge Column

  1. #1
    Board Regular
    Join Date
    Sep 2010
    Posts
    601
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default PQ Dynamic Add Merge Column


    PQ Dynamic Add Merge Column
    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?

  2. #2
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,821
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: PQ Dynamic Add Merge Column

    How are you creating KeyColumns_List ?

  3. #3
    Board Regular
    Join Date
    Sep 2010
    Posts
    601
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: PQ Dynamic Add Merge Column

    Quote Originally Posted by VBA Geek View Post
    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).

  4. #4
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,821
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: PQ Dynamic Add Merge Column

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


    ​does it make it faster?

  5. #5
    Board Regular
    Join Date
    Sep 2010
    Posts
    601
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: PQ Dynamic Add Merge Column

    Quote Originally Posted by VBA Geek View Post
    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!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •