Forum Discussion
Group rows with different by 1 (Power Query)
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 |
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
2 Replies
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_tableHi 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
