Issue merging and expanding afterwards

[WillemM]

Hello,

I'm experiencing an unexpected result in my Powerquery steps. Hope someone can help me understand what is happening or advice on what i'm doing wrong.
The join-step gives exactly the right and expected result for each row...(checked that !) but in the next step on expanding a column from "Inserted Addition"-table something strange happens and PQ makes a mess of the result.

Expectation is to get the date from the next "Statustypevolgnummer" per "Zaakidentificatie"

[jgeddes]

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"

[Chewdata]

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.

[WillemM]

Hi,

Tried, but doesn't change the output.

[Chewdata · Accepted solution]

Have you tried to do a Table.Buffer() before the join?
Buffer = Table.Buffer(#'Changed Type1')
You have to change the input table in merge step to the buffer aswell.

[WillemM]

Hi Chewdata,

Thx. Inserting this step solved the issue. I will look further into this function to see/understand why/how this solved the issue. 

[bhanu_gautam]

@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)

[WillemM]

Hi bhnau_gautam,

Thx. for your fast reply.

Your steps are exactly the steps i followed on each group of Zaakidentificatie.

Everything looks fine until the step when expanding the table, but then i get the unexpected results returned per row.