How to use text.contain to lookup the string in another table's value.

[pheinue]

my original code.  which is too long. I want to use a list to check and reference the output result.

= Table.AddColumn(#"Removed Duplicates", "Custom", each if Text.Contains([项目名称], "淀粉") then "scatch" else if Text.Contains([项目名称], "农产品") then "Argo" else if Text.Contains([项目名称], "玉米") then "Corn" else if Text.Contains([项目名称], "面粉") then "Flour" else if Text.Contains([项目名称], "谷物") then " grain" else if Text.Contains([项目名称], "蘑菇") then "mushroom"else if Text.Contains([项目名称], "植物") then "Plant" else "OTHER")

another table with a key list

Customer activity	Sales Unit	Chinese translation
Starch	Agro	淀粉
Agro	Agro	农产品
Corn	Agro	玉米
Flour/ grain	Agro	面粉/谷物
Mushroom	Agro	蘑菇

[amitchandak]

@pheinue , refer if this can help

https://www.myonlinetraininghub.com/searching-for-text-strings-in-power-query