Forum Discussion

AlanTee's avatar
AlanTee
New Member
3 years ago

Compare column from 2 tables with delimited IDs

Hi All,

I have 2 tables loaded into Power Query that contain a group of IDs within a column (Col1) delimited by comma. For example Table 1 Grouped_ID can be "123456, 654454, 34565" but in table 2 IDGROUP can be "654454, 123456, 34565".

What I am trying to do is for Each Row in Table1, split and list all of the IDs and then iterate over all of the IDGROUP from table 2. and check to see if all of the delimited IDs match if so Return a value from Table 2.

What I have tried is to split each list and then check for a match but it is not providing a positive outcome. Is there anyone who could offer some advice?

let
idList1 = Text.Split([FAC_Group_ID], ", "),
idList2 = Table2[FAC_Group_ID],
idList2Split = List.Transform(idList2, each Text.Split(_, ", ")),
matches = List.Transform(idList2Split, each List.Sort(_) = List.Sort(idList1)),
allMatches = List.AllTrue(matches)
in
if allMatches then "Match" else "No Match"

 

Need Help

1 Reply

  • jennratten's avatar
    jennratten
    Icon for Super User rankSuper User
    3 years ago

    Hello - this is one way you can do it.  Essentially you can split the delimited text to a list, then add a new column to table 1 which returns the value by using List.ContainsAll to compare the two lists.

     

    Table1

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE101EwMzUxMTXRUQDxTJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Col1List", each Text.Split([Col1], ", ")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Table2 Return Value", (x) => Table.SelectRows ( Table2, each List.ContainsAll ( [IDGroupList], x[Col1List] ) ) [ReturnValue]{0})
in
    #"Added Custom1"

    Table2

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjM1MTE10VEwNDI2MTXTUQCRpko6SoZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IDGROUP = _t, ReturnValue = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"IDGROUP", type text}, {"ReturnValue", Int64.Type}}),
    Custom1 = Table.AddColumn(#"Changed Type", "IDGroupList", each Text.Split([IDGROUP], ", "))
in
    Custom1

    Result