Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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...
Need Help
Tips and Tricks
Tutorial Requests
  • dufoq3's avatar
    dufoq3
    2 years ago

    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
dufoq3's avatar
dufoq3
Community Champion
2 years ago

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