Grouping data

[Anonymous]

I am monitoring a real-world event and analysing the data in powerBI. 

Every time this real-world event occurs, exactly 4 events, of types 1,2,3, and 4 are logged to the system. 

Event id 1 will always be first, but the order of the remainin 3 may vary: 

time, event_id, event_data
09:00, 1, xxxxx
09:01, 2, xxxxx
09:02, 3, xxxxx
09:03, 4, xxxxx
09:04, 1, xxxxx
09:05, 4, xxxxx
09:06, 2, xxxxx
09:07, 3, xxxxx

I am looking for a way to group these 4 events each time they occur - probably into a single row - such that I can perform further analysis on them. I suspect I can use event_id 1 as my delimiter, since I know it will always come first.....

event_count, time_1, event_1_data, time_2, event_2_data, time_3, event_3_data, time_4, event_4_data
1, 09:00, xxxx, 09:01, xxxxxx, 09:02, xxxxx, 09:03, xxxx
2, 09:04, xxxx, 09:06, xxxxxx, 09:07, xxxxx, 09:05, xxxx

but am struggling to implement this within powerBI

Any pointers would be very welcome!

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

You can achieve your requirement in PowerQuery.

This M code works:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrC0MjBQ0lEyBOIKEFCK1YGIgkSMMERBIsYYoiAREwxRE6zmmmJVa4bVNnNU22IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [time = _t, event_id = _t, event_data = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"time", type time}, {"event_id", Int64.Type}, {"event_data", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", "event_id", {{"Data", each Table.AddColumn( Table.Sort(_,"event_id"),"Temp",(x)=>Text.From( x[time])&","&x[event_data])[Temp]   }},0,(x,y)=>Number.From(y=1)),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "ID", 1, 1, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"event_id"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ID", "Data"}),
    Custom1 = Table.TransformColumns(#"Reordered Columns",{"Data",each Text.Combine(_,",")}),
    #"Split Column by Delimiter" = Table.SplitColumn(Custom1, "Data", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Data.1", "Data.2", "Data.3", "Data.4", "Data.5", "Data.6", "Data.7", "Data.8"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Data.1", type time}, {"Data.2", type text}, {"Data.3", type time}, {"Data.4", type text}, {"Data.5", type time}, {"Data.6", type text}, {"Data.7", type time}, {"Data.8", type text}})
in
    #"Changed Type1"

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[selimovd]

Hey @Anonymous ,

is there a reason to group the data?

You can analyze better when you let them in the original format.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[Anonymous]

my reason for wanting to group the data is that currently each real-world event generates logged events 1,2,3, and 4 - so 4 rows in my table - but there is nothing to easily flag them as relating to the same real-world event. 

To analyse each real-world event I need some way of logically flagging these 4 events as being related - whether that is by merging them into a single row, or by allocating a unique "real world ID" to each of the 4 rows that I can use as a flag to differentiate them - or perhaps some other method I have yet to come up with

[Anonymous]

I'm wondering if powerBI is the right tool for this....it seems to sit in an odd niche between the data source itself and the presentation to the user....

Conceptually I can see I could loop through the data, taking event 1 as my "real-world-event delimiter" and flagging each of the subsequent events as part of that original event. But I'm not sure this is something I can do in powerBI, as opposed to at the data source itself....

[Anonymous]

Any ideas on this...? 

I could try to pre-process the data outside of powerBI, but it would be nice not to hjave to if possible. 

I looked at using pythoin integration, but I think that then I can not publish to the web...?