Group rows with different by 1 (Power Query)

[Anonymous]

Hi

I have a table where columne "link" is different by 1 from another (but it is one event for subject)
and I need a "subject" group
if link to one row > more than 1 link to another row (and maybe only 1 row in group)

subject	link	event	date	date 2
07-001	12	Visit	04.05.2023
07-001	13	Visit		05.06.2023
07-001	23	Visit	14.05.2023
07-002	45	Visit	15.06.2023
07-002	46	Visit		17.06.2023
07-002	78	Visit	23.05.2023
07-002	79	Visit		25.05.2023
07-003	41	Visit	01.04.2023

subject	link	event	date	date 2	index
07-001	12	Visit	04.05.2023		1
07-001	13	Visit		05.06.2023	2
07-001	23	Visit	14.05.2023		1
07-002	45	Visit	15.06.2023		1
07-002	46	Visit		17.06.2023	2
07-002	78	Visit	23.05.2023		1
07-002	79	Visit		25.05.2023	2
07-003	41	Visit	01.04.2023		1

5 groups as result

	Group Columne with Table
subject	link	event	date	date 2	index
07-001	12	Visit	04.05.2023		1
	13	Visit		05.06.2023	2
07-001	23	Visit	14.05.2023		1
07-002	45	Visit	15.06.2023		1
	46	Visit		17.06.2023	2
07-002	78	Visit	23.05.2023		1
	79	Visit		25.05.2023	2
07-003	41	Visit	01.04.2023		1

[dufoq3 · Accepted solution]

Hi @Anonymous, another solution:

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddDBCcAgDAXQVcSzlSQabRfpRRzAc7s/VQqaWrxEAs//ISlpiBsAaqOR6jjLVe76grfAloBcXZTORkInoGqYLYQXfyBJiIvE1upZwpH2h2GqxvivbjDuApJbV8djSiTuuMP2z6M8D9p6oZGYHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [subject = _t, link = _t, event = _t, date = _t, #"date 2" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"link", Int64.Type}, {"date", type date}, {"date 2", type date}}, "sk-SK"),
    GroupedRowsLocal = Table.Group(ChangedType, {"subject", "link"}, {{"All", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type), type table}}, GroupKind.Local,
        (x,y)=> Number.From( x[subject] <> y[subject] or y[link] - x[link] > 1 ) ),
    CombinedAll = Table.Combine(GroupedRowsLocal[All])
in
    CombinedAll

[AlienSx]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    sort = Table.Sort(Source,{{"subject", Order.Ascending}, {"link", Order.Ascending}}),
    recs = List.Buffer(Table.ToRecords(sort)),
    gen = List.Generate(
        () => [i = 0, r = recs{0}, new_group = true, index = 1],
        (x) => x[i] < List.Count(recs),
        (x) => 
            [
                i = x[i] + 1, 
                r = recs{i}, 
                new_group = (r[link] - x[r][link]) <> 1 or r[subject] <> x[r][subject],
                index = if new_group then 1 else x[index] + 1
            ],
        (x) => 
            x[r] & 
            [subject = if x[new_group] then x[r][subject] else null] & 
            [index = x[index]]
    ),
    to_table = Table.FromRecords(gen)
in
    to_table