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

      • Adem01's avatar
        Adem01
        Frequent Visitor
        2 years ago

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