Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

list.intersect not working case?

Hello, I need to make List.Intersect but found case I am not able to resolve. I tried to simplify to maximum my issue. I have two tables. - One named TPR contains two fields : a identified of issu...
Need Help
  • Smauro's avatar
    Smauro
    5 years ago

    Hi Anonymous 

     

    Could be the case that your column has more characters not shown. Try using Text.Clean on it before adding the new columns.

    More, you should use List.ContainsAny , which should be a faster calculation .

    Best,

    Spyros

Jimmy801's avatar
Jimmy801
Community Champion
5 years ago

Hello Anonymous 

 

you can load it to some fileshare (like onedrive) and put the link here.

But did you change the content of your first row without success?

 

BR

 

Jimmy

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Jimmy

My first row was correct, only my text posted was wrong. but even, I did insert a dummy TX and it failed as well.

I will try the Text cleanup recommended or make some dichotomy to understand where it starts failing...

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Smauro

    The List.ContainsAny could find it, many thanks!

    Custom = List.ContainsAny(Text.Split([TOP],","), Text.Split(TFLT[TOPL]{0},","))

     

    Thanks also to Jimmy for his support

     

    Stchln

    • Smauro's avatar
      Smauro
      Solution Sage
      5 years ago
      Anonymous
      That's great news!

      Let me tell you something though:
      Do you see that the second row is larger than the first one? This is usually a hint that there's a problem with the text displayed somewhere in that row.

      You should definitely transform your [TOP] column with Text.Clean before adding the custom ones, and if I'm right then both columns should have the same height.

      Cheers
      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        I think I found the reason. When there is a '.' character, List.Intersect or List.ContainsAny both fail. Maybe List functions do not consider that it was requested to use the ',' character as separator

        The workaround I have applied in Intersect1.1 is to make a Text.Remove of {"."} before doing the split.

        Intersect1.1 = List.Count(List.Intersect({Text.Split(Text.Remove([TOP],{"."})," "),Text.Split(Text.Remove(TFLT[TOPL]{1},{"."}),",")}))>0