Filter rows by other rows with shared columns in KQL

Question

I have a table with 3 columns

Group	Type	Index
A	Short	1
A	Short	2
A	Long	3
A	Short	4
B	Short	1
...

I want to query and group extract all the rows of that group, but exclude rows with same type and that the index-1 is also exists. For example, if I query Group1, I want to get:

Group	Type	Index
A	Short	1
A	Long	3
A	Short	4

Here we removed the Type1 Index 2 since index 1 already exists with same type.

I tried this query:

traces
| where Group == "A"
| expend OutterType = Type
| where (Index-1) !in((
    traces
    | where Group == "A" and OutterType == Type
    | project Index))

But it says that OutterType doesn't exist in the context of the inner query.

How can filter those rows?

Answer 1

datatable(Group:string, Type:string, Index:int)
[
   ,"A" ,"Short" ,1
   ,"A" ,"Short" ,2
   ,"A" ,"Long"  ,3
   ,"A" ,"Short" ,4
   ,"B" ,"Short" ,1
]
| extend GroupType = strcat(Group, ":", Type)
| partition by GroupType
  (
      project-away GroupType
    | order by Index asc
    | where Index - 1 != prev(Index) 
  )

Group	Type	Index
A	Long	3
B	Short	1
A	Short	1
A	Short	4

Fiddle

Answer 2

let t = datatable(Group:string, Type:string, Index:int)
[
   ,"A" ,"Short" ,1
   ,"A" ,"Short" ,2
   ,"A" ,"Long"  ,3
   ,"A" ,"Short" ,4
   ,"B" ,"Short" ,1
];
t
| join kind=leftanti (t | extend Index = Index + 1) on Group, Type, Index

Group	Type	Index
B	Short	1
A	Short	1
A	Short	4
A	Long	3

Fiddle