Forum Discussion
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 issue, a list - here simplified to one - of tags, field named TOP here
- A second table named TFLT that contains several Filters, per row I have a list of tags separated by a comma
What I need to get is to find the intersection between { [TOP] } in Table TPR and { [TOPL] } in table TFLT
In table TPR, I have one row where my List.Intersect function gives expected result and another row where this is not working. And I cannot figure out what is wrong. I removed any blank, '_'... to put aside basic issues but still this is not working...
TPR table
| PR | TOP | Filter0 | Filter1 |
| P1 | TA | FALSE | TRUE |
| P2 | TOP155G20ATDD10MPBlocker | FALSE | FALSE |
TFLT table
| rank | TOPL |
| {0} | TOP15G20ATDDP7Blocker,TOP15G20ATDD10PDBlocker,TOP15G20ATDDP8Blocker,TOP15G20ATDD01MPBlocker,TOP15G20ATDD10MPBlocker,TOP15G20ATDDeCPRIP7Blocker,TOP15G20ATDDeCPRI10MPBlocker |
| {1} | TA,TB,TD |
where Filter0 is List.Count(List.Intersect({Text.Split([TOP]," "),Text.Split(TFLT[TOPL]{0},",")}))>0
and Filter1 is List.Count(List.Intersect({Text.Split([TOP]," "),Text.Split(TFLT[TOPL]{1},",")}))>0
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjBU0lEKcVKK1QGyjUBs/wBDU3cjA8cQFxdDA98Ap5z85OzUopg8pdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PR = _t, TOP = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PR", type text}, {"TOP", type text}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type", "Filter1", each List.Count(List.Intersect({Text.Split([TOP],","),Text.Split(TFLT[TOPL]{1},",")}))>0),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Filter0", each List.Count(List.Intersect({Text.Split([TOP]," "),Text.Split(TFLT[TOPL]{0},",")}))>0)
in
#"Added Custom4"
Thanks in advance for your help!
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
8 Replies
- SmauroSolution Sage
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
- Jimmy801Community Champion
Hello Anonymous
The result is perfectly fine
The item TOP155G20ATDD10MPBlocker is not in the list created from this TOP15G20ATDDP7Blocker,TOP15G20ATDD10PDBlocker,TOP15G20ATDDP8Blocker,TOP15G20ATDD01MPBlocker,TOP15G20ATDD10MPBlocker,TOP15G20ATDDeCPRIP7Blocker,TOP15G20ATDDeCPRI10MPBlocker.
Only the item TOP15G20ATDD10MPBlocker... that is not the same. one time 15G and the nother 155G
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy- AnonymousNot applicable
Hi Jimmy
Good point, I made a typo reporting my problem but the problem is there. Is there a way to share my .pbix? I see a possibility to share a link but I don't see where to attach a file...
Stchln
- Jimmy801Community Champion
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