Count occurrence of specific string

[Adem01]

In Excel i could easily use the wildcard functions like * and ? to establish a calculation, but i am not able to manage this in Power BI with M or DAX.

I have a table with tickets containing information about the steps taken by an agent to resolve an issue, and i have a table with agent names working in our department. On a daily basis i would like to calculate how many tickets an agent has worked on based on the action field, but the field is a large string with different names and dates and including the time in between, exactly in this format "11-07-2024 17:01 Schilder, Sem:" Below is an example of the tables and the counts captured in the name table per day. Hope there is a solution for this as i am struggling for weeks now to get this done in Power BI.

TicketIDAction

TIC-123	11-07-2024 17:01 Schilder, Sem: Gebeld op 5455, helaas geen gehoor. VM ingesproken. Keeping the ticket open for another 5 days Good evening! Kind regards, Services Center 11-07-2024 16:13 Alfie, Dres: Called, 11-07-2024 15:42 Maher, Roos: Wipe nogmaals gestuurd 10-07-2024 12:33 Tucker, Chris: Wipe started
TIC-124	12-07-2024 12:01 Visser, Jan: Outlook doesnt start correctly 11-07-2024 14:33 Schilder, Sem: Strange issue
TIC-125	12-07-2024 12:21 Bieber, Justin: Anom Not sure if this is het correct one 10-07-2024 07:33 Depp, Johny: Assigning to another resolver
TIC-126	12-07-2024 12:33 Tucker, Chris: Error on startup Browser not supported 11-07-2024 09:00 Tucker, Chris: called, no answer 10-07-2024 12:33 Tucker, Chris: Checked browser on startup
TIC-127	12-07-2024 16:23 Tucker, Chris: resolved 11-07-2024 16:10 Tucker, Chris: wip 11-07-2024 15:25 Tucker, Chris: called, pending 11-07-2024 15:21 Schilder, Sem: : pending user

DateAgentCountOccurencePerTicket

11-7-2024	Schilder, Sem	3
11-7-2024	Alfie, Dres	1
11-7-2024	Maher, Roos	1
11-7-2024	Visser, Jan	0
11-7-2024	Bieber, Justin	0
11-7-2024	Depp, Johny	0
11-7-2024	Tucker, Chris	2

[Adem01]

hi @m_dekorte 

Looks like the query is making some changes and keeps the Agent column only but not splitted on agent name. Also not all dates are extracted. Now I understand the query a little bit more, i must say that this is a really smart solution. 

[m_dekorte]

Hi @Adem01,

Thanks for the new sample, give this a go:

let
    Source = YourSample,
    getFields = Table.AddColumn(Source, "Custom", each 
        [ 
            s = Text.Split( Text.BeforeDelimiter([Actie], ":", 1), " "),
            t = Date.FromText( s{0} ), 
            a = if try t is date otherwise false 
                then [Date = t, Agent = Text.Combine( List.Skip(s, 2), " ") ] 
                else [Date = null, Agent = null]
        ][a], type [Date = date, Agent=text]
    ),
    ExpandFields = Table.ExpandRecordColumn(getFields, "Custom", {"Date", "Agent"}),
    NoBlanks = Table.SelectRows(ExpandFields, each [Date] <> null and [Date] <> ""),
    GroupRows = Table.Group(NoBlanks, {"Date", "Agent"}, 
        {
            {"Count", each Table.RowCount( _ ), Int64.Type}
        }
    )
in
    GroupRows

I hope this is helpful

[Adem01]

hi @m_dekorte ,

This looks better, but not all date and name values are extracted. Like 02-07-2024 08:31 Bakker, Jose: or 28-06-2024 10:03 Bakker, Jose: 

[m_dekorte · Accepted solution]

My bad, here's the revised version.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    getFields = Table.AddColumn(Source, "Custom", each 
        [ 
            s = List.Select( Text.SplitAny( [Actie], "#(cr)#(lf)"), each Text.EndsWith(Text.Trim(_), ":")),
            l = Table.FromRecords( List.Transform(s, (x)=>
                let t = Text.Split(x, " ") in 
                [
                    Date = t{0}?, 
                    Agent = Text.Combine( List.Skip(t, 2), " ")
                ]
            )),
            n = Table.RemoveRowsWithErrors(Table.TransformColumnTypes( l, {{"Date", type date}}), {"Date"})
        ][n])[Custom],
    Combine = Table.Combine(getFields),
    NoBlanks = Table.SelectRows(Combine, each [Date] <> null and [Date] <> ""),
    GroupRows = Table.Group(NoBlanks, {"Date", "Agent"}, 
        {
            {"Count", each Table.RowCount( _ ), Int64.Type}
        }
    )
in
    GroupRows

[Adem01]

This is magic! Thank you.

[PwerQueryKees]

OK. I see what you mean now. I am not sure I understand the exact format of you data, but...

What I would try:

• Remove the ticket number column
• Split the ticket text on delimeter #(lf) to multiple rows
• Filter the rows you need with Text.Contains on just the names. You need some clever code here, but it feasible.
• Split the timestamp field in a date and time part with Test.SplitByDelimeter
• Group by on the date part and the name part.

If you send an xlsx with test data, I will give it a try....

[Adem01]

Thanks for the direction, this shows some posibilities but the dataset can be very (200k records) large and this solution will multiply the rows too much. I couldnt attach the file but i guess you can copy the tables inserted.

[PwerQueryKees]

Have a look at the Text.Contains function. I think it will solve your problem.

[Adem01]

Its a bit difficult especially because of the timestamp in between. I need to count all data for a specific date and name, so the timestamp is irrelevant. 

[m_dekorte]

Hi @Adem01 

Give this approach a go, except for the Custom Column, it leverages the User Interface.

let
    Source = Sample,
    getFields = Table.AddColumn(Source, "Custom", each 
        [ 
            s = Text.Split([Action], " "), 
            t = Date.FromText( s{0} ), 
            a = if try t is date otherwise false 
                then [Date = t, Agent = Text.Combine( List.RemoveMatchingItems(List.Skip(s, 2), {":"}), " ") ] 
                else [Date = null, Agent = null]
        ][a], type [Date = date, Agent=text]
    ),
    ExpandFields = Table.ExpandRecordColumn(getFields, "Custom", {"Date", "Agent"}),
    NoBlanks = Table.SelectRows(ExpandFields, each [Date] <> null and [Date] <> ""),
    GroupRows = Table.Group(NoBlanks, {"Date", "Agent"}, 
        {
            {"Count", each Table.RowCount( _ ), Int64.Type}
        }
    )
in
    GroupRows

With this result

I hope this is helpful

[Adem01]

Your solution looks promising, but i couldnt get it work like your example. What am I doing wrong?

[m_dekorte]

Hi @Adem01,

The column to split in your sample was called "Action" whereas in your production data it is named "Agent" therefore you'll need to update the field reference in the Text.Split function.

I hope this is helpful