Create a column to evaluate if each row contains one value of a list

[qianrusong]

Hello, I have a table with one column called fruit - apple, orange, pear, etc. i want to create a column called Fruit in the master fact table if one column called flavour description contains one of fruits. i do not want to manually type fruits in the created column function. how to address it? thanks a lot.  

Fruit

apple

orange

grape

pear

watermelon

invoice	brand	Flavour Description	Fruit
1	A	red apple	1
2	B	green apple	1
3	C	sweet orange	1
4	C	sour lemon	0
5	A	yummy watermelon	1

[lbendlin · Accepted solution]

let
  Fruits = {"apple", "orange", "grape", "pear", "watermelon"}, 
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "TYw7CoAwEAWvEra28XcA9RjBIuAjTX6sCcHbu/7AYpoZGK2ppYYmgbEpk5IDrY2mTswsWAbCz/fiFmGvQFaRTbBPGL4QCysHH8Otx/d+FO8PVU0Ge7grric=", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [invoice = _t, brand = _t, #"Flavour Description" = _t]
  ), 
  #"Added Custom" = Table.AddColumn(
    Source, 
    "Fruit", 
    each List.Min({List.Count(List.Intersect({Text.Split([Flavour Description], " "), Fruits})), 1}), 
    Int64.Type
  )
in
  #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[qianrusong]

thanks for your answer. I tried to replace source - table. My master table has previous ETL steps in advanced query and fruit is an excel loaded to the query. How to make changes above to fit with my real model? thanks for your suggestions. 

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[qianrusong]

thanks @lbendlin . I found the way to adjust it. Definitively, you gave me insights to move forwards. thanks again.