Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

look up semicolon seperated text from another table without split function

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.

Need Help
Show and Tell
  • AlexisOlson's avatar
    AlexisOlson
    3 years ago

    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"

5 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    3 years ago

    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"
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    3 years ago

    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"