Counting rows for event sequences

[Johannes_Swarts]

Hello,

I have data that looks like this:

Data consists of date/time (down to millisecond), a work item ID, and events within the work item.  Colors designate different types of events - top few events are masked for confidentiality reasons.

What I'm trying to do is to collapse the work item into a time sequence of single events representing either 1) single event occurrences, or 2) blocks of the same event.

Goal:

time stamp

work item ID

event type

count of event types

For example, see the orange event sequence (Current Layer Changed).  Data shows 6 different occurrences in sequence.  I'd like to see a single row - timestamp    workitem_id      event_function     6

There are slicers enabled for user, project, date (day).

My knowledge of Power BI is at the beginner/intermediate level.  I have a pretty good working knowledge of DAX.

Thanks in advance!

Hans

[danextian]

Hi @Johannes_Swarts ,

If you're looking for something like the screenshot below, you can make use of an extra parameter when grouping a table in Power Query which groups rows by sequence. In a sequence of A-B-C-A-A-B-C-C-C, A-A and C-C-C will each own group, the rest will each have their own as well. After grouping you can then add an index column to identify the groups.

Here's a sample code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddJJCoAwEETRq0jWQnoyRncOtwje/xoquJL89YOuoujWktSskk0sBo1VljSmLV3jH1QIlMAJgqAQzA/sPagEC4C9dY8eGIETTFDXCoVXOvXWPTvgtK4bAa3rE4R7IaC6TusGfUl86143", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Timestamp = _t, Event = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Timestamp", type datetime}, {"Event", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Event"}, {{"Grouped", each _, type table [Timestamp=nullable datetime, Event=nullable text]}}, GroupKind.Local),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "RowCount", each Table.RowCount([Grouped])),
    #"Expanded Grouped" = Table.ExpandTableColumn(#"Added Custom", "Grouped", {"Timestamp"}, {"Timestamp"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Grouped",{"Timestamp", "Event", "Index", "RowCount"})
in
    #"Reordered Columns"

You may also try this approach that uses DAX instead:

https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Cthulhu/m-p/509739#M211 

[Selva-Salimi]

Hi @Johannes_Swarts 

you just need to use a table in visualisation pane

add each of these column in columns part:

and then select first/last or max/min for timestamp, work item_id and first event function. add first event function twice to the columns part (drag and drop). select count for the second one. 

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.

[Johannes_Swarts]

Hello Selva-Salimi,

Thanks for the reply!  Simple solution, which I like.

However, not exactly what I was looking for...  🙂

Yous solution in table at lower right, orginal table at upper left.

Your solution gives the total count of each event type in the work item, along with the first date/timestamp.  I'm looking for a more detailed view that shows the event type count and first date/time for each GROUP of same events in the work item.  I expect to see, taking Extents Changed as an example:

date/time 1     Extents Changed     count A

date/time 2     Extents Changed     count B

date/time 3     <some other event>     count C

date/time 4     Extents Changed     count D

etc

:

:

 Maybe another column trick?

Thanks,

Hans

[Johannes_Swarts]

Oops - in my example above, pretend the first Events Changed in some other event.

Sorry!