Forum Discussion
Solved
Retrieve Multiple Values from Another Table
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!
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
2 Replies
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_v2Hi 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!
