Isolating individual contains text

[RichOB]

Hi, how can I split these issue results into individual counts from this table?:

Issue

Medical Concern

Medical Concern; ASB - Violence

ASB - Violence

Medical Concern; Theft; Missing Person

Theft

Theft; Missing Person

I can isolate the issues by the individual ContainsString Count measures, but in my real-life scenario I have hundreds of "Issues", so I need this to be more automated for all issue types instead of creating 100 individual measures, but not sure how?

I need the outcome to show:

Medical Concern	3
Asb - Violence	2
Theft	3
Missing Person	2

[Elena_Kalina]

Hi @RichOB 

1. Use the "Split Column by delimiter" in Power Query

2 Create the measure like this:

IssueCount = COUNT(YourTable[Issue])

 

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[RichOB]

Hi @Elena_Kalina thanks for your reply. I have followed these steps for my real-life scenario. Unfortunately, the count is the same as if I didn't do the delimiter, but it has now added a blank row with a count of 600. Any ideas why it would add a blank row?  

[Elena_Kalina · Accepted solution]

The blank row issue typically occurs because:

1. The source column contains empty values before splitting

2. There are consecutive semicolons (;;) in the data, creating empty entries when splitting

Solutions

* Clean data before splitting:

= Table.TransformColumns(#"PreviousStep", {{"Issue", each if _ = null then "" else _, type text}})

* Remove extra delimiters:

= Table.TransformColumns(#"PreviousStep", {{"Issue", each Text.Replace(_, ";;", ";"), type text}})

*Split and filter out blanks:

#"SplitColumn" = Table.SplitColumn(#"PreviousStep", "Issue", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Issue"})
#"Trimmed" = Table.TransformColumns(#"SplitColumn", {{"Issue", Text.Trim, type text}})
#"Filtered" = Table.SelectRows(#"Trimmed", each [Issue] <> "" and [Issue] <> null)

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[RichOB]

This is great, thanks so much!

[rajendraongole1]

Hi @RichOB  - I have attached pbix file for your reference.

Please check the file

Snippet FYR:

Output:

Hope this works.

[RichOB]

Hi @rajendraongole1  thanks for your reply. I have followed these steps for my real-life scenario. Unfortunately, the count is the same as if I didn't do the delimiter, but it has now added a blank row with a count of 600. Any ideas why it would add a blank row?