Power Query Merge: Expand Column loads 200 MILLION+ records?

Hello -

Once again, I seem to find new and exciting issues with power query. I've searched high and low, but I haven't had any luck... I'd love to know the inner working of power Query that causes the following issue:

Setup:
I have two tables (just tables, no external connections) as the source for two queries:

Query A has about 100k records and only two columns.
Query B has about 700k records and four columns.

There is a common column between the two that I am using to merge (Left Outer - All from Query A and Matching from Query B). My expected result is 100k rows, since I only want to get the rows from Query B that match Query A.

The merge itself is fine (expected 100k records) in about 10 seconds, but when I try to expand the table (Query B) to only add one column (for a total of three columns, two from Query A and one from Query B) and then load the result of the query....

It goes and goes and goes... I've never actually let it all load, since I get impatient after 45 minutes and 200 MILLION records....

Any idea why simply expanding one column for the matching records in Query/Table B is causing Power Query to try and load hundreds of millions of records?


Thanks to anyone who can help me understand!

- Dave

Hi,

Re

Originally Posted by Trioptre

but when I try to expand the table (Query B) to only add one column (for a total of three columns, two from Query A and one from Query B) and then load the result of the query....

it might be more useful not to lump those two actions into one, and instead to clarify whether it's the load which takes up all the time or whether you also experience a delay in the table expansion.

Regards

I can't reproduce your issue. I created a set-up which mimics the one you describe, with identical numbers of rows in each query, and the expansion was near instantaneous.

This may only be solvable by seeing the actual workbook, though perhaps someone else on this forum will be able to resolve this for you without going down that road.

Regards

Apologies, I see that "with identical numbers of rows in each query" was misleading. I meant identical numbers to those that you are using.

I also loaded the data into a worksheet table near instantantaneously.

Regards