Count how many times a text appears in a string

[Anonymous]

Good afternoon with everyone, I write in this forum hoping you can help me. The problem to be solved is as follows:

I have a text field, in some cases quite extensive in which I have to separate strings by the delimiter, and additionally I have to increment a counter every time 14 text abbreviations appear in those strings.

#1;04/08/2020 16:12;2400264; NLLA;#2;04/08/2020 17:23;2400264; NCO;#3;12/08/2020 16:51;2400264; NCO;#4;13/08/2020 9:45;2400264; VLL;#5;19/08/2020 10:31;2400264; NCO

For example that's a record, it contains 5', every time it starts a state (it means an attempt to communicate from a call center), what I have to count is how many times it appears to me: NLLA, NCO, VLL and so with 14 possible states. In the example I need to get NLLA-1, NCO-3, and VLL-1, the sum of that gives me 5 and that I compare it to a field in which I already have the number of attempts stored in another variable.

The first thing I tried is to look for a DAX function, M Language, or function in Power Query but I didn't find anything to help me. Then with the Text.Split function I separated the string by the delimiter, but as a result I got each string separated down into 5 rows. In that scenario I don't know what to do. Please help you, if I should even save in each column (14 columns) the sum of each of the states, there is no problem. There are cases where I have 40 chains, i.e. 40 attempts. For example, a column called NLLA, NCO, VLL could be created and the remaining 11 and each one must store for each record as follows:

NLLA NCO VLL

1 3 1

Thank you from now on for your valuable help.

[Syndicate_Admin]

Hello @v-xuding-msft ! I tried what is indicated for a case I have, but the LEN function does not show what corresponds... What is its function exactly?

[amitchandak]

@Anonymous , not very clear to me

Try like the example

new column = List.Count(Text.Split([column], "NLLA"))

[Anonymous]

Thank you very much, but it was the first thing I already did and it doesn't work for me because it creates a list down with each of the separate strings.

[mahoneypat]

Text.Split is the right start, and you can use List.Count and List.FindText from there.  See M code example below.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XcwxDoAgEAXRqxA4ALvLgsKvjC3RzoZw/2vYCVjPy7RmHYPU0+6FhAynwgJRIkkKc9V6wMkitiJhEucNF8AyLyL/gILDB3LROPpTK1wE5zGgEtaB7f0F", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "NewList", each Text.Split([Column1], ";")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "NCO Count", each List.Count(List.FindText([NewList], "NCO"))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "NLLA Count", each List.Count(List.FindText([NewList], "NLLA"))),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "VLL Count", each List.Count(List.FindText([NewList], "VLL")))
in
    #"Added Custom3"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Thank you very much for the answer, but I am confused where I should place the column that contains the text, the text column is called ZIntentos.

[v-xuding-msft · Accepted solution]

Hello @Silviadav ,

Sorry I'm late.

You can replace column1 with ZIntentos. For example:

NLLA =
 (
    LEN ( 'Table'[ZIntentos] ) - LEN ( SUBSTITUTE ( 'Table'[ZIntentos], "NLLA", "" ) )
)
    / LEN ( "NLLA" )

NCO =
 (
    LEN ( 'Table'[ZIntentos] ) - LEN ( SUBSTITUTE ( 'Table'[ZIntentos], "NCO", "" ) )
)
    / LEN ( "NCO" )

VLL =
 (
    LEN ( 'Table'[ZIntentos] ) - LEN ( SUBSTITUTE ( 'Table'[ZIntentos], "VLL", "" ) )
)
    / LEN ( "VLL" )

[Anonymous]

Thank you very much, this served as a solution.

[Anonymous]

Thank you very much, I will try it and tell you if it works.

[v-xuding-msft]

Hi @Anonymous ,

You also could create calculated columns with DAX to implement it.

NLLA =
 (
    LEN ( 'Table'[Column1] ) - LEN ( SUBSTITUTE ( 'Table'[Column1], "NLLA", "" ) )
)
    / LEN ( "NLLA" )

NCO =
 (
    LEN ( 'Table'[Column1] ) - LEN ( SUBSTITUTE ( 'Table'[Column1], "NCO", "" ) )
)
    / LEN ( "NCO" )

VLL =
 (
    LEN ( 'Table'[Column1] ) - LEN ( SUBSTITUTE ( 'Table'[Column1], "VLL", "" ) )
)
    / LEN ( "VLL" )