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

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