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
PwerQueryKees
2 years agoSuper User
OK. I see what you mean now. I am not sure I understand the exact format of you data, but...
What I would try:
- Remove the ticket number column
- Split the ticket text on delimeter #(lf) to multiple rows
- Filter the rows you need with Text.Contains on just the names. You need some clever code here, but it feasible.
- Split the timestamp field in a date and time part with Test.SplitByDelimeter
- Group by on the date part and the name part.
If you send an xlsx with test data, I will give it a try....
- Adem012 years agoFrequent Visitor
Thanks for the direction, this shows some posibilities but the dataset can be very (200k records) large and this solution will multiply the rows too much. I couldnt attach the file but i guess you can copy the tables inserted.