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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!