Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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
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.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
@devika , check if this can work in the power query. I have not tested this
List.Count(Text.Split([column], "CallTime"))
User | Count |
---|---|
107 | |
89 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |