How do I count occurrences of every specific text in a column?

[Anonymous]

I want to count text in a column, but the difference here from other posted questions is that I want to count every occurrence of every individual word in the column. For example, each row in the column can contain one of more first names, and I want to count occurrences of every individual name separately. I am trying to avoid having to write a DAX formula for each individual name since there are a lot of names. I am trying to figure out a single Dax formula that will count occurrences every name. Remember, each row can contain more than one name, so I can't just count the rows. I need to count individual words. Any help would be appreciated. Thanks!

[Anonymous]

Hi @Anonymous ,

What does your raw data look like? Could you please provide some sample data(exclude sensitive data) with Text format? What is the final result you want? For example, you have a text column in the table below and the final value you want to get is Jack: 2, Helen: 2 and Jessie: 3?

First Name	Occurrences
Jack	2
Helen	2
Jessie	3

Also, is there a first name dimension table like the one below in your model?

In addition, you can refer the following links to get it by Power Query method...

Count the number of times a value appears in a column in Power Query

How to number each occurrence of a substring in a cell in Power Query?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKijKTylNLtHNS8xN1csqSFfSUTJWitWJVsovyUgt0oVKQ2VMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Image Base Name" = _t, ImageQty = _t]),
    AddImageIDs = Table.AddColumn(
        Source,
        "ImageIDs",
        each
            Text.Combine(
                List.Transform(
                    {1..Number.FromText([ImageQty])},
                    (x)=>
                        Text.BeforeDelimiter([Image Base Name],".") &
                        "-" &
                        Text.From(x) &
                        "." &
                        Text.AfterDelimiter([Image Base Name],".")
                ),
                " "
            )
    )
in
    AddImageIDs

Best Regards

[HotChilli]

 Does it have to be DAX?

Can you count the spaces in the field (and add 1)?