Forum Discussion

sanjanarama's avatar
sanjanarama
Resolver I
4 years ago
Solved

comparing values with comma

IDparent NamesFamily NamesMatch
1Apple, OrangeFruit, Lemon, Grapesno
2Lemon,grapes, FruitGrapes, Fruit, Lemonyes
3Hello, Fridge, DoorFridge, hellono
4balconyBalconyyes
6tree, timetimeno


two columns with comma seperated values, output should show , match or not matched.

I tried below m query but it is not working 

if List.ContainsAll(Text.Split([parent Names],","),Text.Split([Family Names],",")) then "Yes" else "No"


Can anyone help me

I do not want to perform pivot/unpivot

Need Help
  • CNENFRNL's avatar
    CNENFRNL
    4 years ago
    = Table.AddColumn(#"Changed Type", "Match",
        each let
                p = List.Transform(Text.Split([parent Names], ","), Text.Trim),
                f = List.Transform(Text.Split([Family Names], ","), Text.Trim)
            in List.Count(p) = List.Count(f) and List.Count(List.Difference(p, f, Comparer.OrdinalIgnoreCase)) = 0
    )

2 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    4 years ago
    = Table.AddColumn(#"Changed Type", "Match",
        each let
                p = List.Transform(Text.Split([parent Names], ","), Text.Trim),
                f = List.Transform(Text.Split([Family Names], ","), Text.Trim)
            in List.Count(p) = List.Count(f) and List.Count(List.Difference(p, f, Comparer.OrdinalIgnoreCase)) = 0
    )