Search if values exist in another table and returning values from column (Power query Editor)

[rauerfc]

Hi, I have one table A and a cross-reference table = "table B" that I want to return Value from column "SU".

Table A:

Customer Consignee Reported Key	Type	Loc
22137	A	AS
9878	B	DAS
23563	C	DG
6322	D	ASD
26468	E	AS
13473	S	D
14739	F	GF
18831	D	ASD
18196	E	A
26551	F	FG
23236	GH	ASDASD
20749	S	ASD
11952	A	C

Table B:

Customer Consignee Reported Key	SU
26468	154
26468	555
13473	483
14739	120

First I checked if the value exists in Table B:

if List.Contains(Cross_ref[Customer], [Customer Consignee Reported Key]) = true then

If True I want to return the value from "SU" column like a lookup function? Is this possible?

If false the script will search using another column from data frame.

[Anonymous · Accepted solution]

Hi @rauerfc ,

As @lbendlin said, you may try Merge to replace M syntax in Power Query. Belowis my method, please check.

Since there are two values for Customer 26468——154 and155, I transformed the Table B by using Table.Group() and Text.Combine() firstly:

#"Grouped Rows" = Table.Group(#"Changed Type" , {"Customer"}, {{"Combine Values", each Text.Combine(List.Transform(_[SU], (x) => Number.ToText(x)), " , "), type text}})

Then please use the following formula to create a new custom column to Table A:

try (let currentCustomer = [Customer Consignee Reported Key] in Table.SelectRows(#"Table B", each [Customer] = currentCustomer)){0}[Combine Values] otherwise "No matched"

The final output is shown below:

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

[lbendlin]

What do you expect to happen for 26468 ? It has two matches in Table B.

You don't need to do lookups in Power Query. You do merges, or custom column generators.