Forum Discussion
Helper IIssue merging and expanding afterwards
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.
Super UserHere 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)
Helper IHi 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.