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

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.