Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Adem01
Frequent Visitor

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

 

1 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

View solution in original post

12 REPLIES 12
Adem01
Frequent Visitor

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. 

Adem01_0-1721031494444.png

 

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

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: 

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

This is magic! Thank you.

PwerQueryKees
Super User
Super User

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....

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
Super User
Super User

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

 

 

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. 

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

m_dekorte_0-1720942742333.png

I hope this is helpful

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

Adem01_0-1720950442467.png

 

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

 

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.