none
Match Multiple Values (Comma Delimited) RRS feed

  • Question

  • I have a problem that I am trying to solve in Excel Power Query whereby I need to be able to take a single-column table of comma-delimited values, and determine which can be looked up in another table. The number of items in each row is variable, 1...n, the second is a single column of single value. For instance, the first table might look like

    abc, def, fgh
    value1, value2, value3, value4

    and the second might be

    abc
    fgh
    value3
    value4

    The result I am seeking is either an additional column, or alternatively an amended original column, with the matched values,

    abc, fgh
    value3, value4

    I can easily split the comma-delimited values into separate columns and create extra columns that identify matches and then join up those matches, but that is very cumbersome and is not future-proofing when I later get a row that has even more values (and it's procedural not functional).

    I also tried creating a recursive function to take each value in turn split it, lookup, and append matching values, but I couldn't get that working.

    My other thought was to take the existing column and remove any non-matching values, but that looks like a similar recursive function that I failed to get working.

    Anyone have a solution?

    TIA

    Friday, March 22, 2019 4:01 PM

Answers

  • Hello,

    Try using Text.Split([Column name],",")  where "," is your delimiter. It will create a new (single) column with a list values.

    This list column can be expanded to new rows (using a column header button)

    Don't forget to Trim the final column as there may be remaining space characters.

    Warm regards

    • Marked as answer by Pips51 Friday, March 22, 2019 11:34 PM
    Friday, March 22, 2019 5:08 PM

All replies

  • Hello,

    Try using Text.Split([Column name],",")  where "," is your delimiter. It will create a new (single) column with a list values.

    This list column can be expanded to new rows (using a column header button)

    Don't forget to Trim the final column as there may be remaining space characters.

    Warm regards

    • Marked as answer by Pips51 Friday, March 22, 2019 11:34 PM
    Friday, March 22, 2019 5:08 PM
  • Thanks Jakub,

    That did it nicely.

    I added an index, split the column, matched them, merged the rows over the index … job done.

    I was trying Table.SplitColumn, Text.Split made it so much simpler.

    Thanks again.

    Friday, March 22, 2019 11:30 PM