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 ,

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

    This is magic! Thank you.