Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
stchln
Resolver I
Resolver I

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

PRTOPFilter0Filter1
P1TAFALSETRUE
P2TOP155G20ATDD10MPBlockerFALSEFALSE
 

TFLT table

rankTOPL
{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!

 

1 ACCEPTED SOLUTION
Smauro
Solution Sage
Solution Sage

Hi @stchln 

 

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




Feel free to connect with me:
LinkedIn

View solution in original post

8 REPLIES 8
Smauro
Solution Sage
Solution Sage

Hi @stchln 

 

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




Feel free to connect with me:
LinkedIn

Jimmy801
Community Champion
Community Champion

Hello @stchln 

 

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

 

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

Jimmy801
Community Champion
Community Champion

Hello @stchln 

 

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

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...

Hi @Smauro

The List.ContainsAny could find it, many thanks!

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

stchln_0-1602768696823.png

 

Thanks also to Jimmy for his support

 

Stchln

@stchln
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



Feel free to connect with me:
LinkedIn

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

stchln_1-1602914195974.png

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors