Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
# Apples =
CALCULATE(
COUNTROWS(SampleData),
CONTAINSSTRING(SampleData[Fruit],"Apple")
)
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:
Proud to be a Super User!
# Apples =
CALCULATE(
COUNTROWS(SampleData),
CONTAINSSTRING(SampleData[Fruit],"Apple")
)
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:
Proud to be a Super User!
Thank you so very much. The information above was tremendously helpful in solving my issue.
User | Count |
---|---|
96 | |
85 | |
77 | |
66 | |
63 |
User | Count |
---|---|
110 | |
96 | |
96 | |
67 | |
59 |