Find the values in a column which "StartsWith" any of the value from another column

[Anonymous]

Hello Experts,

Good Day!
I am stuck with finding the values from  a column which "StartsWith" any of the value from another column.

Example - 

I have two tables with one column each

Table 1

Type

Cat

Dog

Bag

Ball

Hello

Table 2

Object

Dogs

Cat Food

School Bag

BaseBall

!HelloWorld

I need output like below

Output

Object	Flag
Dogs	TRUE
Cat Food	TRUE
School Bag	FALSE
BaseBall	FALSE
!HelloWorld	FALSE

Logic - If table 2 values starts with table 1 values then Flag column returns TRUE else false eventhough Table 1 value present in table 2 value in middle.

Please assist.

Thanks in advance...!!!

[CNENFRNL · Accepted solution]

let
    Lookup = {"Cat","Dog","Bag","Ball","Hello"},
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcslPL1aK1YlWck4sUXDLz08Bc4KTM/LzcxScEtPBXKfE4lSnxJwcMEfRIzUnJz88vygHqDYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Object = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Flag", each List.Accumulate(Lookup, false, (s,c) => s or Text.PositionOf([Object], c, null, Comparer.OrdinalIgnoreCase)=0))
in
    #"Added Custom"

[gvrajesh · Accepted solution]

Flag =

VAR SO = Table2[Object]

VAR Tab = FILTER(Table1, CONTAINSSTRING(LEFT(SO,LEN(Table1[Type])),Table1[Type]))

RETURN

IF( COUNTROWS(Tab) > 0, TRUE(),FALSE())

[Icey · Accepted solution]

Hi @Anonymous ,

In addition to @CNENFRNL 's and @gvrajesh 's methods, you can also try to create a calculated column like so:

Column =
IF (
    SUMX (
        Table1,
        FIND (
            UPPER ( Table1[Type] ),
            LEFT ( UPPER ( Table2[Object] ), LEN ( Table1[Type] ) ),
            ,
            0
        )
    ) > 0,
    TRUE (),
    FALSE ()
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gvrajesh · Accepted solution]

Flag =

VAR SO = Table2[Object]

VAR Tab = FILTER(Table1, CONTAINSSTRING(LEFT(SO,LEN(Table1[Type])),Table1[Type]))

RETURN

IF( COUNTROWS(Tab) > 0, TRUE(),FALSE())

[CNENFRNL · Accepted solution]

let
    Lookup = {"Cat","Dog","Bag","Ball","Hello"},
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcslPL1aK1YlWck4sUXDLz08Bc4KTM/LzcxScEtPBXKfE4lSnxJwcMEfRIzUnJz88vygHqDYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Object = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Flag", each List.Accumulate(Lookup, false, (s,c) => s or Text.PositionOf([Object], c, null, Comparer.OrdinalIgnoreCase)=0))
in
    #"Added Custom"