cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cspress
Regular Visitor

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:

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!

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Thanks both for these answers. Both work really well.

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-yulgu-msft
Microsoft
Microsoft

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
)

1.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.