Retrieve Multiple Values from Another Table

[Katiedog1]

Hello,

I have 2 tables (Table A & Table B).  I want to Use 'Animal Categories' in Table B to lookup the corresponding 'Animal Type' in Table A that will give me all Animal Types.  See example tables below.  Appreciate the help in advance!

[dufoq3 · Accepted solution]

Hi @Katiedog1, check also this:

Result

let
    TableA = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcslPV9JR8k3MzU3MUYrViVZyTixBFfDJzM9DFXHMS0/NScsszgAKu4EokKBTaU5ORmpiCrKYc2JRSmYeUJeOklNmUQpErCi/HJnvlFOampVYCReKBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Categories = _t, #"Animal Type" = _t]),
    TableB = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRcslPj8lzTiyJyXMqzcnJSE1MUYrViVZycXUDSjonFqVk5iXmxOT5ZObngSXcPTwhupRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Zoo Type" = _t, #"Animal Categories" = _t]),
    Ad_CatHelper = Table.AddColumn(TableB, "CatHelper", each Text.SplitAny([Animal Categories], "#(lf)#(cr)"), type list),
    Ad_v1 = Table.AddColumn(Ad_CatHelper, "v1", each Text.Combine(List.Transform([CatHelper], (x)=> TableA{[Categories = x]}[Animal Type]), "#(lf)"), type text ),
    Ad_v2 = Table.AddColumn(Ad_v1, "v2", each 
        [ a = List.Transform([CatHelper], (x)=> TableA{[Categories = x]}[Animal Type]),
          b = List.Accumulate(List.Zip({{0..List.Count(a)-1}, a}), {}, (s,c)=> s &
                { if List.Contains(s, c{1}) then "" else if c{0} = List.Last(List.PositionOf(a, c{1}, Occurrence.All)) then c{1} else "" } ),
          c = Text.Combine(b, "#(lf)")
        ][c], type text)
in
    Ad_v2

[Anonymous]

Hi @Katiedog1 

Firstly split the 'Animal Categories' column into rows by delimiter #(lf), then merge Table A to Table B by using Categories column as matching column. 

Split columns by delimiter - Power Query | Microsoft Learn

Merge queries overview - Power Query | Microsoft Learn

Result:

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!