Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Item code
82300apple78693
64852banana56329
83211cabbage28922
86851lettuce36397
23967basil45000
15442apple58605
51484pumpkin93240
286lemon98777
12478thyme38443
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!
Solved! Go to Solution.
Hi,
Try this calculated column formula.
=LOOKUPVALUE(Item_type_mapping[Type],Item_type_mapping[Item],FIRSTNONBLANK(FILTER(VALUES(Item_type_mapping[item]),SEARCH(Item_type_mapping[Item],'Item'[Item Code],1,0)),1))
Hope this helps.
Hi,
Try this calculated column formula.
=LOOKUPVALUE(Item_type_mapping[Type],Item_type_mapping[Item],FIRSTNONBLANK(FILTER(VALUES(Item_type_mapping[item]),SEARCH(Item_type_mapping[Item],'Item'[Item Code],1,0)),1))
Hope this helps.
Thanks both for these answers. Both work really well.
You are welcome.
Hi @cspress,
You could create a calculated table with below formula:
New table = FILTER ( CROSSJOIN ( 'Item Table', 'Type Table' ), IF ( ISERROR ( FIND ( [Item], [Item code] ) ), 0, 1 ) = 1 )
Best regards,
Yuliana Gu
User | Count |
---|---|
139 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |