Forum Discussion

cspress's avatar
cspress
Regular Visitor
8 years ago
Solved

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!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    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.

4 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    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.