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 ma...
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
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