topic Re: Group rows with different by 1 (Power Query) in Power Query

Group rows with different by 1 (Power Query)

Anonymous — Sun, 14 Jul 2024 05:07:44 GMT

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

Re: Group rows with different by 1 (Power Query)

AlienSx — Sun, 14 Jul 2024 06:52:34 GMT

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

Re: Group rows with different by 1 (Power Query)

dufoq3 — Wed, 17 Jul 2024 07:57:16 GMT

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