cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Count the frequency of a specific value in a column with multiple comma separated fixed values?

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.

 

topics.jpg

Many thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

Re: Count the frequency of a specific value in a column with multiple comma separated fixed values?

@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:

 

image.png

 

Then use "Group-By" option to just group the topic and count, the output will be as follows:

 

image.png

 

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"

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Highlighted
Super User I
Super User I

Re: Count the frequency of a specific value in a column with multiple comma separated fixed values?

@ChrisBernatek Could you please post the sample data (able to copy and paste) which will be helpful to replicate and resolve the issue quicker.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: Count the frequency of a specific value in a column with multiple comma separated fixed values?

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

 

Highlighted
Super User I
Super User I

Re: Count the frequency of a specific value in a column with multiple comma separated fixed values?

@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:

 

image.png

 

Then use "Group-By" option to just group the topic and count, the output will be as follows:

 

image.png

 

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"

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Frequent Visitor

Re: Count the frequency of a specific value in a column with multiple comma separated fixed values?

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors