I have a dataset that has values as following
DataSet:
Col1 Col2
A 0
B 1
C 1
D 1
E 0
F 0
G 1
H 0
I 0
J 0
K 1
I want the result to be as follows:
Col1 Col2
[A] 0
[B, C, D] 1
[E, F] 0
[G] 1
[H, I, J] 0
[K] 1
or Alternatively as:
Col1 Col2
[B, C, D] 1
[G] 1
[K] 1
Is there any way to achieve this using kql?
I have looked into using prev function but it only works if there is fixed number of rows for grouping everytime. but here I need to group n rows based on a value in column, but the groups need to separate every time the same value is started again.
Here you go:
The output will be exactly what you wanted.
Note: I use
serialize
, but you can useorder by
as well - but you need at least one of them, so that the records will be ordered.