Is there a Power Query or DAX Equivalent for this code in Excel?

[Anonymous]

This code gets put into cell A3, and checks A2 for which Fruit is in the list of food items. Once it searches and finds the fruit, it places the fruit name in the cell A3. 

=IFNA(IFS(ISNUMBER(SEARCH("Apple",A2)),"Apple",
ISNUMBER(SEARCH("Banana",A2)),"Banana",
ISNUMBER(SEARCH("Orange",A2)),"Orange",
ISNUMBER(SEARCH("Grape",A2)),"Grape"),"")

Where A2 = "Carrot Bread Orange Eggs"
Result A3 = "Orange"

Is there an equivalent formula that can be created in Power Query or DAX that can do this task? It would be even better if it could reference a list of fruit in a column rather than being hardcoded. 

Thanks!

[AlB · Accepted solution]

Hi @Anonymous 

Yes you can first define the list:

      RefList_ = {"Apple", "Banana", "Orange", "Grape"},

then you can add a custome column with the code

      = if List.Contains(RefList_, [ColumnA2]) then [ColumnA2] else null  

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

This M code will search for each word in the ItemList and return all matching words from the Items column, in a new column.

Here's a sample PBIX file with the code

let
    ItemList = {"Apple", "Banana", "Orange", "Grape"},
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sKsovUXAqSk1MUfAvSsxLT1VwTU8vVoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Items = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Items", type text}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Matches", 
    
        each List.Accumulate
            (
                ItemList,

                "",

                (state, current) => 
                
                    if List.Contains(Text.Split([Items], " "), current) 
                    
                    then state & " " & current 
                    
                    else state
                
            ))
in
    #"Added Custom"

String matching is case sensitive.  If you want it to be case insensitive then add Comparer.OrdinalIgnoreCase to List.Contains :

 List.Contains(Text.Split([Items], " "), current, Comparer.OrdinalIgnoreCase) 

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[wdx223_Daniel]

Power Query = List.Skip({"Apple", "Banana", "Orange", "Grape"},each not Text.Contains(A2text,_)){0}?

DAX =MAXX(FILTER({"Apple", "Banana", "Orange", "Grape"},SEARCH([Value],A2text,,0)),[Value])

[AlB · Accepted solution]

Hi @Anonymous 

Yes you can first define the list:

      RefList_ = {"Apple", "Banana", "Orange", "Grape"},

then you can add a custome column with the code

      = if List.Contains(RefList_, [ColumnA2]) then [ColumnA2] else null  

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers