Smartsheet+PowerBi Count Rows that Contain Formula

[swilder]

Hello!

I am trying to get a formula to count the number of times a word or phrase appears in a column. The data is from a Smartsheet, pulled into PowerBi. The Smartsheet column type is a multi-select drop-down, but in PowerBi the column type is simply Text.

Below is an example with generic info that represents the data and issues I am having. 

Day	Fruit
Monday	Apple
Tuesday	Banana, Apple
Wednesday	Pear

I would like a formula that would return 2 for Apple, as it is listed twice. 

The current formulas (below) return 1, even though the word apple appears twice in the column.

#Apples =

CALCULATE(

COUNTROWS('Table1'),

'Table1'[Fruit] IN ({"Apple"})

)

Count of Apples =

CALCULATE(

COUNTA('Table1']),

'Table1'[Fruit] IN { "Apple" }

)

Any other formula ideas would be greatly, greatly appreciated!

[bolfri · Accepted solution]

Direct answer:

# Apples = 
CALCULATE(
    COUNTROWS(SampleData),
    CONTAINSSTRING(SampleData[Fruit],"Apple")
)

Better solution:

Use Power Query M editor to split Fruit's into rows.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PS0msVNJRciwoyElVitWJVgopTS2GiDkl5gGhjgJCLjw1JQ8mG5CaWKQUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Day = _t, Fruit = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,", ",",",Replacer.ReplaceText,{"Fruit"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value", {{"Fruit", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Fruit"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Fruit", type text}})
in
    #"Changed Type1"

BetterData:

[swilder]

Thank you so very much. The information above was tremendously helpful in solving my issue.