Forum Discussion
Grouping data
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!
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.
5 Replies
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 regardsDenisBlog: WhatTheFact.bimy 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
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....
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.
