Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |