Issue merging and expanding afterwards

WillemM 

Here is a general approach to achieve your goal:

Sort the Data: Sort your data by "Zaakidentificatie" and "Statustypevolgnummer" to ensure the rows are in the correct order.
Add an Index Column: Add an index column to the sorted data to help identify the "next" row within each group.
Merge Queries: Perform the join operation to merge the tables.
Expand Columns: Expand the necessary columns from the joined table.
Filter Rows: Use the index column to filter and select the "next" status type number for each "Zaakidentificatie".
Here is an example of how you might add an index column and then merge:

// Step 1: Sort the data
SortedTable = Table.Sort(Source, {{"Zaakidentificatie", Order.Ascending}, {"Statustypevolgnummer", Order.Ascending}}),

// Step 2: Add an index column
IndexedTable = Table.AddIndexColumn(SortedTable, "Index", 1, 1, Int64.Type),

// Step 3: Merge Queries
MergedTable = Table.NestedJoin(IndexedTable, {"Zaakidentificatie", "Index"}, OtherTable, {"Zaakidentificatie", "Index"}, "Inserted Addition", JoinKind.LeftOuter),

// Step 4: Expand Columns
ExpandedTable = Table.ExpandTableColumn(MergedTable, "Inserted Addition", {"DesiredColumn1", "DesiredColumn2"}),

// Step 5: Filter Rows (if necessary)
FilteredTable = Table.SelectRows(ExpandedTable, each [Index] = [Index] + 1)

Hey!

I see that your Statustypevolgnummer is datatype any, which means that it is treated as text. that means that with sorting 11 comes after 1. This might be the problem when adding the index and addition.

If you want to try a completely different approach that does not use the nested join here is an example code...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldLNasQgEAfwVxHPQsavhHjsbV+hSw6WTLM2iynR3efvqNuyl5b0IA4D/x8j4/nMobdGgtG654L/FEOnVKdASSa1s+CgNF+9X9mCOy4h5R1xn0uEDtR7Er9pYyfhoUlnlLOWmqeY/YrMv5O4xUKND04dprSTUKk5YJxjiMudxqPpMNKgecOcck1XVXD9D9kMVb5stxmv4WNlbxcfqayPlhUT3PwN6gbC6AyZJfSC6XoLmW2fC6Z8qliDBLeHMeXU8ITdfcqFw+Y1S/D+sKcdDN8LflqIbAp9Bz5NXw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Zaakidentificatie = _t, Zaaknummer = _t, DatumStatusGezet = _t, StatustypeOmschrijving = _t, Statustypevolgnummer = _t, Index = _t, Addition = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Zaakidentificatie", Int64.Type}, {"Zaaknummer", Int64.Type}, {"DatumStatusGezet", type datetime}, {"StatustypeOmschrijving", type text}, {"Statustypevolgnummer", Int64.Type}, {"Index", Int64.Type}, {"Addition", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Zaakidentificatie"}, {{"AllRows", each _, type table [Zaakidentificatie=nullable number, Zaaknummer=nullable number, DatumStatusGezet=nullable datetime, StatustypeOmschrijving=nullable text, Statustypevolgnummer=nullable number, Index=nullable number, Addition=nullable number]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"AllRows", each Table.AddColumn(_, "next", (r)=> try [DatumStatusGezet]{List.PositionOf([Statustypevolgnummer], List.Min(List.Select([Statustypevolgnummer], each _ > r[Statustypevolgnummer])))} otherwise null, type datetime)}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(Custom1, "AllRows", {"Zaaknummer", "DatumStatusGezet", "StatustypeOmschrijving", "Statustypevolgnummer", "Index", "Addition", "next"}, {"Zaaknummer", "DatumStatusGezet", "StatustypeOmschrijving", "Statustypevolgnummer", "Index", "Addition", "next"})
in
    #"Expanded AllRows"