Forum Discussion

Bassehave's avatar
Bassehave
Helper I
3 years ago

Looking for specific text in cell based on another column

I'm trying to do a Find/Mid/Match in Power Query, but I'm not sure how to do it.

 

Basically, I have a long list of article numbers and article names - inside these articles names it's stated which colour the product is. The issue is that it's not always in the same place.

 

I have a query named "X_Colours" with one column "Colours" type is Text.

Other query is "Items" with two columns "Item No" (Number) and "Name" (Text).

 

I would like a custom column that looks in "Items" "Name" for a similar value from "X_Colours" "Colours" column. If it finds the same value, it should return the value from "X_Colours" if not, it should just return something like "Unknown", so I can manually look for the colour and add it to the list.

 

Is this possible or do I have to create a new table and use an excel formula and then add it back into the query?

Thanks in advance! 

find
Find multiple values
lookup
Match

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Bassehave ,

     

    A new column in Table 2 , Items. In DAX

     

    new column =

    var _max = maxx(filter(X_Colours, search(X_Colours[Color], Item[Name],,0) >0) , X_Colours[Color])

    return

    if(isblank(_max), "Unknown", _max)

    • Bassehave's avatar
      Bassehave
      Helper I
      3 years ago

      Hi Amitchandak,

      Thanks a lot for the solution and I'm sure its what I need, but I can't seem find where to use this formula. 

      When going to "Add Column" and "Custom Column" it doesn't work when I write this formula.

      Is it possible to convert it to this window?

      I'm trying to do this in Excel Power Query, so I'm not sure if I miss some functionality compared to Power BI query.

       

      Thanks in advance!