look up semicolon seperated text from another table without split function

[sachin_s]

Hi,
I am a newbie to the powerBI tool.
I would like to seek community help to get some guidance on how to achieve the Requirement on power BI power query.
I have a table that has a product column that either has 1 product or multiple products separated by ';".
example

Product

Pappaya green;Apple(Kashmir)

Pappaya green;Kiwi (Udan (Stand Alone))

Pappaya green;Jack;Orange

Pappaya green;Pomo (Seedless (Stand Alone))

Banana (Red)

And I have a  product list mentioned below

Prod List

Jack

Apple(Kashmir)

Dates

Cashew

Pappaya green

Kiwi (Udan (Stand Alone))

Banana (Red)

Pomo (Seedless (Stand Alone))

lemon

melon

Grape

cane

Orange

Requirement:
Now I want Table 1's first row compare with Table 2 and whichever is the word that matches first from Table 2, it returns that value in a newly created column in Table 1.
For the above example, the expected results in Table 1's new column should be
 

Mapped Product list

Apple(Kashmir)

Pappaya green

Jack

Pappaya green

Banana (Red)

Guidance to resolve this puzzle would be greatly appreciated as this can become a game-changer to my project.
I have seen @AlexisOlson  helping a lookup issue(not the same, but small similarities )
Solved: Lookup data from another table when text exists - Power Platform Community (microsoft.com)
Would be of great help if some help can be provided on my requirement.

[sachin_s]

Hi @Vijay_A_Verma ,
Thanks for the solution. This worked perfectly.

[AlexisOlson · Accepted solution]

I think we can do this more simply than @Vijay_A_Verma suggests.

Take the first element of the intersection of Table2[Prod List] and the products in the current row.

List.First(List.Intersect({Table2[Prod List], Text.Split([Product], ";")}))

Here's a fully self-contained variation of this query:

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgsKEisTFRIL0pNzbN2LCjISdXwTizOyM0s0lSK1UFX4J1ZnqmgEZqSmKegEVySmJei4JiTn5eqiU2tV2JytrV/UWJeeioW2YD83HygGampKTmpxcXYTHNKzANCBY2g1BSgQCwA", BinaryEncoding.Base64), Compression.Deflate)), type table [Product = (type text)]),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcxBCsIwEIXhq4SuUvASVUGwC4viqnTxaB61mExCUije3pC4lNl8/MzMODZXzO9mOoxNF4Kl7pFebo1tSWdsTEWnnLkXDggBH6glklJKv+6r0k8DUfqxQYzqrBe29ckRkkfpO00Ng3c+L5LGMqV/J5bO19eO9qdLRGDRDKm4RciSOX0B", BinaryEncoding.Base64), Compression.Deflate)), type table [#"Prod List" = (type text)]),
    ProdList = List.Buffer(Table2[Prod List]),
    #"Added Custom" = Table.AddColumn(Table1, "Mapped Product List", each
        let
            CurrRowProds = Text.Split([Product], ";"),
            SharedProds = List.Intersect({ProdList, CurrRowProds})
        in
            List.First(SharedProds),
        type text
    )
in
    #"Added Custom"

[sachin_s]

Thanks @AlexisOlson for providing the optimized solution.

[Vijay_A_Verma]

@AlexisOlson Brilliant stuff!!!

[Vijay_A_Verma]

Use this code in a custom column

Table2[Prod List]{List.Min(List.Transform(Text.Split([Product], ";"), (x)=> List.PositionOf(Table2[Prod List], x)))}

To handle error and show "None Found"

try Table2[Prod List]{List.Min(List.Transform(Text.Split([Product], ";"), (x)=> List.PositionOf(Table2[Prod List], x)))} otherwise "None Found"

Complete code in action

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgsKEisTFRIL0pNzbN2LCjISdXwTizOyM0s0lSK1UFX4J1ZnqmgEZqSmKegEVySmJei4JiTn5eqiU2tV2JytrV/UWJeeioW2YD83HygGampKTmpxcXYTHNKzANCBY2g1BSgQCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table2[Prod List]{List.Min(List.Transform(Text.Split([Product], ";"), (x)=> List.PositionOf(Table2[Prod List], x)))})
in
    #"Added Custom"