Forum Discussion
Post ProdigyPower Query Merge / expand and retain grouping / why is grouping Lost?
As always the caveat is that you have to validate for yourself which of the many ways to do lookups/joins/merges in Power Query is applicable in your scenario, both from a performance and a scalability perspective. Thankfully we don't have to guess - we can use the query diagnostics to measure and know.
As I said the best join is the one you don't do.
What do you mean by "grouping"?
Power Query merge operations do not guarantee the sort order after the merge, unless you buffer the table before the merge. Even if you sort them before the merge, the order may change. Power query does not like to bring the whole table in-memory, which it has to do with a sort operation.
--Nate
Super UserWhat do you mean by "grouping"?
Post Prodigy i mean grouping, I merge see sample ; A merged to all the A's, but when I expand the
they are are not kept together
- ronrsnfld
If I understand correctly, you would like to to a merge where the order of "ItemL" in the first table is preserved.
With Table.NestedJoin the program chooses the Join Algorithm to use, and that will not necessarily provide those results.
However, with "Table.Join" you can choose the Join Algorithm, and the right hash is what you want:
Table.Join(TableA, {"ItemL"}, TableB, {"Item"}, JoinKind.LeftOuter, JoinAlgorithm.RightHash)Results
Whether this will be more efficient than other methods (eg adding an Index column) is something you will need to test using trial and error.