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

      • m_dekorte's avatar
        m_dekorte
        Resident Rockstar
        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