Forum Discussion
Merge problems - incorrect expand
TL;DR - Table.Buffer() fixed issue
Also had this issue. Completely incorrect join results after merge (Left outer), with seemingly no sense as to how it was processing join and delivering expanded columns (this was joining on multiple columns)
Attempted numerous fixes: data types, clean & trim, recreating entire structures from scratch - spent 24hrs on this blinkin issue
As above - added Table.Buffer() to the table which formed the right side of the join (this table was gettings its data from a source table which contained a Group By and index). It worked!
Thanks all for tip
I have found a solution which is much, much faster than using Table.Buffer. I got the idea from here:
https://docs.microsoft.com/en-us/power-query/commonissues#preserving-sort
This can be done after joins or inside groupings.
It doesn't have to be a sort, it can also be something else like adding an index column. There are several M table functions that tell the engine to handle a table in a sorted manner. Below examples of what I did. This performs much faster than using Table.Buffer.
Fix data order for a join. Fix inside the NestedJoin function:
Note: you have to experiment which side of the join you have to fix.
= Table.NestedJoin(Table.Sort(Source, {"Index", Order.Ascending}), {"Index"}, Table1, {"Index"}, "Table1Fixed", JoinKind.LeftOuter)
= Table.NestedJoin(Source, {"Index"}, Table.Sort(Table1, {"Index", Order.Ascending}), {"Index"}, "Table1Fixed", JoinKind.LeftOuter)
= Table.NestedJoin(Table.Sort(Source, {"Index", Order.Ascending}), {"Index"}, Table.Sort(Table1, {"Index", Order.Ascending}), {"Index"}, "Table1Fixed", JoinKind.LeftOuter)
Fix data order for a grouping. Fix inside the Group function:
Note: here I have to sort both the table the AddIndexColumn function and the FirstN function.
= Table.Group(Table2, {"w", "x", "y"}, {{"GroupTable", each Table.AddIndexColumn(Table.Sort(_, {"Index", Order.Ascending}), "FirstValueInColumnZ", Table.FirstN(Table.Sort(_, {"Index", Order.Ascending}), 1)[z]{0}, 1), type table}})