Forum Discussion
Solved
Grouping by consecutive data in column
Good afternoon everyone, I have some speed test data, these can be successful or failed. I need to set up consecutive failed test groups. This is in order to find the start date and end date of each of the groups. At the moment this is my data
What I'm hoping for as a next step is to get to this: (Create the ID Group column)
And as a final result I need this:
which is the product of the following analysis:
Consider the example table...
You can end up with the following...
Using the code...
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc67CsAgDIXhV5HMgiaN160tdO4uvv9r1KlwzHAyffxkDOKgQaKoY+5JydN53eu+a0zTbyD94FkTCzKAw4ICQC2o8EOyoDlI5E1IjxESxQKGQrVAADSa8wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Code = _t, Result = _t, Index = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Code", type text}, {"Result", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Result"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Records", each _, type table [Date=nullable datetime, Code=nullable text, Result=nullable text]}},GroupKind.Local), #"Group Failed" = Table.AddIndexColumn(Table.SelectRows(#"Grouped Rows", each [Result] = "F" and [Count] <> 1), "Group", 1, 1), #"Removed Columns" = Table.RemoveColumns(#"Group Failed",{"Result", "Count"}), #"Expanded Records" = Table.ExpandTableColumn(#"Removed Columns", "Records", {"Date", "Code", "Result", "Index"}, {"Date", "Code", "Result", "Index"}), #"Non Consecutive" = Table.SelectRows(#"Grouped Rows", each [Result] <> "F" or [Count] = 1), #"Removed Columns1" = Table.RemoveColumns(#"Non Consecutive",{"Result", "Count"}), #"Expanded Records1" = Table.ExpandTableColumn(#"Removed Columns1", "Records", {"Date", "Code", "Result", "Index"}, {"Date", "Code", "Result", "Index"}), #"Combine Tables" = Table.Combine({#"Expanded Records1", #"Expanded Records"}), #"Sorted Rows" = Table.Sort(#"Combine Tables",{{"Index", Order.Ascending}}) in #"Sorted Rows"The key is the GroupKind.Local in the Table.Group step.
Hopefully this helps.
1 Reply
Consider the example table...
You can end up with the following...
Using the code...
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc67CsAgDIXhV5HMgiaN160tdO4uvv9r1KlwzHAyffxkDOKgQaKoY+5JydN53eu+a0zTbyD94FkTCzKAw4ICQC2o8EOyoDlI5E1IjxESxQKGQrVAADSa8wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Code = _t, Result = _t, Index = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Code", type text}, {"Result", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Result"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Records", each _, type table [Date=nullable datetime, Code=nullable text, Result=nullable text]}},GroupKind.Local), #"Group Failed" = Table.AddIndexColumn(Table.SelectRows(#"Grouped Rows", each [Result] = "F" and [Count] <> 1), "Group", 1, 1), #"Removed Columns" = Table.RemoveColumns(#"Group Failed",{"Result", "Count"}), #"Expanded Records" = Table.ExpandTableColumn(#"Removed Columns", "Records", {"Date", "Code", "Result", "Index"}, {"Date", "Code", "Result", "Index"}), #"Non Consecutive" = Table.SelectRows(#"Grouped Rows", each [Result] <> "F" or [Count] = 1), #"Removed Columns1" = Table.RemoveColumns(#"Non Consecutive",{"Result", "Count"}), #"Expanded Records1" = Table.ExpandTableColumn(#"Removed Columns1", "Records", {"Date", "Code", "Result", "Index"}, {"Date", "Code", "Result", "Index"}), #"Combine Tables" = Table.Combine({#"Expanded Records1", #"Expanded Records"}), #"Sorted Rows" = Table.Sort(#"Combine Tables",{{"Index", Order.Ascending}}) in #"Sorted Rows"The key is the GroupKind.Local in the Table.Group step.
Hopefully this helps.