Number of Occurrence of a text

[devika]

Hi All,

I'm trying to find number of occurence of  "Call Time" appeared in each cell/value and add it as an additonal column.

Here is my sample text from SQL database column. The "CallTime" is appeared twice and I'm looking to add that count into  a New column. The Call time value may appear upto 100 times in a that text

ConversationWith:"Mi (RA) > AlfC","NotetakerUsername":"Mic","CommunicationContent":null},{"Id":"fb74e50a-dd65-4de0-9e70-8857bf18934a","LastUpdated":"2021-10-04T20:59:41.14+11:00","CallTime":"2021-10-04T20:58:00+11:00","Content":"? id - no, not on 3 West either","UpdatedBy":null,"ConversationWith":"Mic > CarmC","NotetakerUsername":"Micn","CommunicationContent":null},{"Id":"012abddc-86c0-4c90-ae06-02ed09092f3a","LastUpdated":"2021-10-04T21:04:35.586+11:00","CallTime":"2021-10-04T21:02:00+11:00","Content":"? where pt might be - not sure\nLanded at 1920 hours","UpdatedBy":null,"ConversationWith

Any ideas please let me know.

[devika · Accepted solution]

Hi All,

Thanks for all your solutions.

I'm excited to share that I've learned something new, I have parse that column as JSON and added custom formula to count on the list.

List.Count([CaseJson.Notes])

It was easier than I thought

[KNP]

Hi @devika,

This will do the job and I have tested.

PBIX file attached also.

@amitchandak's answer is missing the '-1' (and an explanation) that is neccessary otherwise you'll get one too many.

The Text.Split splits the list based on "CallTime" (and removes it), the List.Count just counts the rows left in the list. So if "CallTime" could ever be the very first or very last thing in the string then this may not work.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldC9TsMwFAXgV7nKBKKurp2fJllQyYRUGFArhrqDE9/QiMSpEgeEEO+OlbYIGGgZPPn46Phbr72sNS/U9cpWrXms7DaV3l0FFw/zS5ADok8wr8tMehPp3beWrHqmbtVTZ1RD0hvTxXibtU0zmKoYm1yrJWNdwAx1/TF5l96tHuNlPgsoRMW0jkIWaEKW0AxZHIezvORx4gdq7Fuo3q52WlnaPxQoOOPIMFgKTMMkDfiUB1ecp4j7Aaqul9Vh1a907ELfo1/zpHcNlQYGpp24Y6E14MMj9RbIaVA35g87bt4OH9pX/HA7WhzVMtU1J9jMf9yQC5VrXbA4KpAFRYJMEUYMBWlMMBGlf9rNAQSpH07DODoHziXEX3CvzodgZ6GpnrYWchodLfRDR1KahTKaNCgLPBEI23bo+nM5vc3mEw==", 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", "Custom", each List.Count(Text.Split([Column1],"CallTime"))-1)
in
    #"Added Custom"

I think there's likely a more elegant solution using @ImkeF function found here, where you could split the list based on " (quotes) and then count the actual value in the list. Or she may have a function that already does this.

Either way, I hope this helps.

[amitchandak]

@devika , check if this can work in the power query. I have not tested this

List.Count(Text.Split([column], "CallTime"))