Find text within a column and return simplified version
I'm new to PowerBI but pretty experienced in Excel which is probably a hindrance as much as a help. Something I've done in Excel and would like to replicate in Power BI is to search within the text of a range of cells, find specific text and then return a simplified version. I'd like to do this for multiple search terms and multiple simplified results, and ideally have a table as my basis for the searches rather than lots of nested IF statements.
Probably easier to explain with an example:
If I had the following "Item code" column where the text I'm looking for is surrounded by other 'useless' characters which could be numbers or letters:
I'd like to run it past the following table with "Item" and "Type" columns: Item Type apple fruit banana fruit lemon fruit cabbage vegetable lettuce vegetable pumpkin vegetable basil herb thyme herb
So as to add a new "Type" column to the original table:
Item code Type 82300apple78693 fruit 64852banana56329 fruit 83211cabbage28922 vegetable 86851lettuce36397 vegetable 23967basil45000 herb 15442apple58605 fruit 51484pumpkin93240 vegetable 286lemon98777 fruit 12478thyme38443 herb
It feels like there should be a simple solution to this but I'm struggling to find it!