How to speed up merge in Power Query when use a NestedJoin?

Question

I am merging two queries together in PowerQuery. Below is the m code:

= Table.NestedJoin(#"Calculate Sumif weight of Delivery", {"Delivery.Delivery Level 01"}, Trax, {"Delivery Document"}, "Trax data ship date 7 19-3 20 fbid", JoinKind.LeftOuter)

The merge is taking forever to process...is there anything I can do to speed it up?

lbendlin · Answer

Just before the merge use the Table.AddKey step to slap indexes onto the fields on both sides of the join.

https://docs.microsoft.com/en-us/powerquery-m/table-addkey

This step is not reachable from the UI, you have to do it manually in Advanced Editor.

anonymous · Answer

Thanks, the match is a many to many so there isn't a unique key in either table. Will that still help? I won't be able to merge on the newly created key.

lbendlin · Answer

only one way to find out -&nbsp; use the Power Query diagnostics and do some timing tests.

anonymous · Answer

Can I add an Index Column, does that accomplish the same goal?

lbendlin · Answer

Only if you then use that index column for the merge.

Forum Discussion

How to speed up merge in Power Query when use a NestedJoin?

5 Replies

Related content

Tips on speeding up Table.NestedJoin

MERGE QUERIES

Table.View - Handling NestedJoin + Expand

Async merge risk

Speed - Database vs Delta Files