Forum Discussion
Search if values exist in another table and returning values from column (Power query Editor)
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.
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.
2 Replies
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
Super User
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.
