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

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