Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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 |
---|---|
78 | |
74 | |
62 | |
61 | |
45 |
User | Count |
---|---|
108 | |
100 | |
91 | |
83 | |
61 |