Check if substring from column exists inside string in another table column

Question

I have two tables, firstone identifiants, which has id column as below :

second table informations, which has information id column as below:

I added a custom column check in identifiants table to check if id exists (TRUE) or not (FALSE) in information id column in informations tabe:

but it give me for all ids FALSE even some of them exists in information id.

please what's the solution for that?

horseyride · Accepted Answer · 2022-11-20 23:02:43Z

1

try

=  Text.Contains(Text.Combine(informations[information id]," ") ,Text.From([id]))

EDIT

try

let CheckList=List.Combine(Table.AddColumn(informations, "Custom", each Text.SplitAny([information id],"/?!"))[Custom]),
...
#"Added Custom" = Table.AddColumn(#"PriorStepName", "Custom", each if List.Count(List.Select(CheckList, (cat) => Text.EndsWith( cat, [id] )))=0 then false else true)

edited Nov 20, 2022 at 23:02

answered Nov 19, 2022 at 0:04

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mourad BENKDOUR Over a year ago

Excellent answer but it resolve only 50% of the problem, please have look to the edit post

Mourad BENKDOUR Over a year ago

I have an 352A5KL which it's a substring of other id 352A5KL75 So, to get TRUE the id 352A5KL must be alone ended by space or / or ? or ! or it's in the end of the cell. so id 352A5KL should be FALSE, because there is not cell in information id column that has this id.

horseyride Over a year ago

Edited. Please stop changing requirements after posting question

Collectives™ on Stack Overflow

Check if substring from column exists inside string in another table column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related