Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I have two tables and I want to search a text from one table to the other :
Fruits_List
Fruit_Name |
Apple |
Banana |
Grape |
Shop
Merchandize_Description |
Yellow big zise Banana |
Apple |
Kids toy |
I want to have the following column output:
Shop
Merchandize_Description | Is_Fruite |
Yellow big zise Banana | Yes |
Apple | Yes |
Kids toy | No |
I tried different formulas but I keep getting wrong output. Everything I tried gives me the right answer only when the values are exactly matching:
Merchandize_Description | Is_Fruite |
Yellow big zise Banana | No |
Apple | Yes |
Kids toy | No |
Here are the formulas I tried:
Is_Fruit = IF(Calculated(COUNTROWS(Fruits_List), FILTER(Fruits_List, FIND(Fruits_List[Fruit_Name], EARLIER(Shop[Merchandize_Description]),,-1)))>0), "Yes","No")
Is_Fruit = IF(Calculated(COUNTROWS(Fruits_List), FILTER(Fruits_List, CONSTAINSSTRING(Shop[Merchandize_Description], Fruits_List[Fruit_Name],,-1)))>0), "Yes","No")
Is_Fruit = IF(Calculated(COUNTROWS(Fruits_List), FILTER(Fruits_List, ISNUMBER(SEARCH(Fruits_List[Fruit_Name], EARLIER(Shop[Merchandize_Description]),1,Blank())))>0), "Yes","No")
I feel I am missing something. Any advice?
Thank you,
B
Solved! Go to Solution.
Hi,
This calculated column formula works
=IF(SUMX(Fruits,FIND(UPPER(Fruits[Fruit_Name]),UPPER(Merchandise[Merchandize_Description]),,0)) > 0,"Yes","No")
Hope this helps.
Hi,
This calculated column formula works
=IF(SUMX(Fruits,FIND(UPPER(Fruits[Fruit_Name]),UPPER(Merchandise[Merchandize_Description]),,0)) > 0,"Yes","No")
Hope this helps.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
83 | |
64 | |
60 | |
56 |
User | Count |
---|---|
183 | |
111 | |
105 | |
77 | |
70 |