Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, am new to Power BI so trying to get to grips with some calculated measures.
I have a column in a table that contains multiple comma separted 'topic' values.
I need a formula that will count the number of individual occurances of each unique topic so that I can create a simple chart to show what topics are featuring the most.
See image below for example of the data that is saved in each row - there is typically more than 1 topic in any one cell and the topics are listed in different orders, there are also a lot of blanks. I need a way of just being able to count the number of occurances of each of the topics please.
In excel I would just list out the individual topics in a table (there are only 16 unique values) and then use a "COUNTIFS" formula to count how many times each value appears in the range.
Need to know how to do this in Power BI.
Many thanks
Solved! Go to Solution.
@ChrisBernatek Thanks for providing the sample data.
Here is the solution, that is achieved in "Power Query". You can use "Split-Column" option and make sure you split them into rows as below:
Then use "Group-By" option to just group the topic and count, the output will be as follows:
For your reference, here is the steps I've followed.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pVTLDoIwEPwVwrkH4sG7gQ8gIcYDcih1xY3Qmj4w/r2GCJZAgcZbu93dmZ1Om+fhCcpAAZXsFhYkDzNagwrOJop2+yAD2SID8svplpy2WFGNgnfbhxQXw3TABNfAdddmnDaELCCrZ3xIulAqkSGvevQEamxBvty8HAVOVuk3pqAGNhBbgp2ZY3s6meCRWPArVkZSLaRaV4o4TueGO/I7F891jkui+V2yq1MsmkeNzSdtrnSqisseywbydGqCSksszUj4v03tkmCCNj+0fWf9K/B2upd7bER7vVECd4k399HQ49/A95mERfEG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [TextData = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"TextData", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"TextData", "Topic"}}), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Renamed Columns", {{"Topic", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Topic"), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Topic", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type1", {"Topic"}, {{"Count", each Table.RowCount(_), type number}}) in #"Grouped Rows"
Proud to be a PBI Community Champion
@ChrisBernatek Could you please post the sample data (able to copy and paste) which will be helpful to replicate and resolve the issue quicker.
Proud to be a PBI Community Champion
There are 20,000 rows total, but here is a sample of what each cell typically contains in the topic column:
Web search
Sales & Service,Web search,Web navigation,Web product content
Web navigation
Web search
Web search,CAD
Pricing & Delivery
Sales & Service,Pricing & Delivery,Web product content
Product selection
Pricing & Delivery
Web navigation
Pricing & Delivery
Web navigation,Product selection,Configurators
Web navigation
Web search,Web navigation
Web product content
Unknown
Pricing & Delivery
Pricing & Delivery,Web search,Web navigation,Web product content
Pricing & Delivery,Compliment,Web product content,Product selection
Web search
Web navigation
Web search
Sales & Service,Web search,Web navigation,Distributors
Web search,Web navigation,Web product content
Web navigation
Pricing & Delivery,Distributors
Product selection
Unknown
CAD
Sales & Service,Pricing & Delivery,Web navigation
Web search,Web navigation
Unknown
Unknown
Web navigation,Web product content
Unknown
Web navigation
Sales & Service,Pricing & Delivery
CAD
Web search,CAD,Configurators
Web navigation
Web search
The unique topic values are as follows:
Web search
Web navigation
Web product content
Product selection
Sales & Service
CAD
Pricing & Delivery
Compliment
Configurators
Distributors
No suggestion
Career / HR / Corp
Wants to be contacted
Product quality
No response provided
Many thanks
@ChrisBernatek Thanks for providing the sample data.
Here is the solution, that is achieved in "Power Query". You can use "Split-Column" option and make sure you split them into rows as below:
Then use "Group-By" option to just group the topic and count, the output will be as follows:
For your reference, here is the steps I've followed.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pVTLDoIwEPwVwrkH4sG7gQ8gIcYDcih1xY3Qmj4w/r2GCJZAgcZbu93dmZ1Om+fhCcpAAZXsFhYkDzNagwrOJop2+yAD2SID8svplpy2WFGNgnfbhxQXw3TABNfAdddmnDaELCCrZ3xIulAqkSGvevQEamxBvty8HAVOVuk3pqAGNhBbgp2ZY3s6meCRWPArVkZSLaRaV4o4TueGO/I7F891jkui+V2yq1MsmkeNzSdtrnSqisseywbydGqCSksszUj4v03tkmCCNj+0fWf9K/B2upd7bER7vVECd4k399HQ49/A95mERfEG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [TextData = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"TextData", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"TextData", "Topic"}}), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Renamed Columns", {{"Topic", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Topic"), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Topic", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type1", {"Topic"}, {{"Count", each Table.RowCount(_), type number}}) in #"Grouped Rows"
Proud to be a PBI Community Champion
Thanks for the solution but I am looking to achieve the same result but by using a Measure.
Is this possible?
For example - I would want a DAX formula to be able to count the number of times the word "CAD" appears in the topic column.
I have a simple Measure that can count the number of times the word "CAD" appears, but it only counts it when that is the only word in that column - it doesn't count up the times where "CAD" appears along with other topics in the cell. Ie where the data looks like this: "CAD,Web Search,Complimnent"..
Is there a DAX formula that can just count up the number of times a word appears, regardless of the cells having other comma separated words in it?
Many thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |