Forum Discussion

Adem01's avatar
Adem01
Frequent Visitor
2 years ago
Solved

Count occurrence of specific string

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-12311-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-12412-07-2024 12:01 Visser, Jan:
Outlook doesnt start correctly


11-07-2024 14:33 Schilder, Sem:
Strange issue
TIC-12512-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-12612-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-12712-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-2024Schilder, Sem3
11-7-2024Alfie, Dres1
11-7-2024Maher, Roos1
11-7-2024Visser, Jan0
11-7-2024Bieber, Justin0
11-7-2024Depp, Johny0
11-7-2024Tucker, Chris2

 

Need Help
  • m_dekorte's avatar
    m_dekorte
    2 years ago

    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

12 Replies

  • PwerQueryKees's avatar
    PwerQueryKees
    Super User
    2 years ago

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

     

     

    • Adem01's avatar
      Adem01
      Frequent Visitor
      2 years ago

      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's avatar
        m_dekorte
        Resident Rockstar
        2 years ago

        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

  • PwerQueryKees's avatar
    PwerQueryKees
    Super User
    2 years ago

    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's avatar
      Adem01
      Frequent Visitor
      2 years ago

      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.

  • Adem01's avatar
    Adem01
    Frequent Visitor
    2 years ago

    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's avatar
      m_dekorte
      Resident Rockstar
      2 years ago

      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's avatar
        Adem01
        Frequent Visitor
        2 years ago

        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: