Forum Discussion
Adem01
2 years agoFrequent 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 informa...
- 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
2 years agoFrequent Visitor
Your solution looks promising, but i couldnt get it work like your example. What am I doing wrong?