Stuck in Excel Power Query Merge

%3CLINGO-SUB%20id%3D%22lingo-sub-1540192%22%20slang%3D%22en-US%22%3EStuck%20in%20Excel%20Power%20Query%20Merge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1540192%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20using%20power%20query%20merge%20function%20for%20some%20time%20to%20do%20sort%20of%20%22Vlookup%22%20for%20my%20company's%20heavy%20data%20(over%2010m%20rows).%3C%2FP%3E%3CP%3EThe%20data%20I%20want%20to%20extract%20normally%20is%20below%20100k%20rows%2C%20so%20I%20will%20%22Get%20data%20from%20Table%2F%20range%22%20and%20create%20a%20connection%2C%20then%20I%20will%20%22Get%20data%20from%20folder%22%2C%20and%20create%20connection%20for%20the%2010m%2B%20rows%20of%20data.%3C%2FP%3E%3CP%3EThen%20I%20will%20merge%20the%20data%20from%20the%20table%2C%20to%20the%20ones%20to%20the%20folder.%3C%2FP%3E%3CP%3EIt%20works%20fine%20until%20now%2C%20taking%20less%20than%2030mins%20to%20finish%20the%20job%2C%20and%20not%20heavy%20at%20all.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20now%2C%20when%20I%20try%20to%20%3CFONT%20color%3D%22%23FF0000%22%3Eexpand%20the%20data%20during%20merge%2C%20the%20power%20query%20tends%20to%20stuck%20at%20some%20point%3C%2FFONT%3E%2C%20normally%20at%20200MB%20(200k%2B%20rows%20of%20my%20data).%3C%2FP%3E%3CP%3EI%20have%20tried%20to%20use%20my%20less%20heavy%20data%20for%20a%20test%2C%20and%20those%20that%20have%2020MB%2B%20(20k%2B%20rows)%20and%2080MB%2B%20(80k%2B%20rows)%20worked%20fine%2C%20only%20the%20heavy%20ones%20keep%20stucking%20at%20some%20point.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThey%20will%20move%20like%201MB%20after%2030mins%2C%20then%20after%202%20or%203%20hours%2C%20a%20message%20saying%20something%20about%20OLE%20action%20popped%20up.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHopefully%20someone%20can%20provide%20some%20advise%20on%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1540192%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Frequent Visitor

Hi

 

I have been using power query merge function for some time to do sort of "Vlookup" for my company's heavy data (over 10m rows).

The data I want to extract normally is below 100k rows, so I will "Get data from Table/ range" and create a connection, then I will "Get data from folder", and create connection for the 10m+ rows of data.

Then I will merge the data from the table, to the ones to the folder.

It works fine until now, taking less than 30mins to finish the job, and not heavy at all.

 

But now, when I try to expand the data during merge, the power query tends to stuck at some point, normally at 200MB (200k+ rows of my data).

I have tried to use my less heavy data for a test, and those that have 20MB+ (20k+ rows) and 80MB+ (80k+ rows) worked fine, only the heavy ones keep stucking at some point.

 

They will move like 1MB after 30mins, then after 2 or 3 hours, a message saying something about OLE action popped up.

 

Hopefully someone can provide some advise on this.

 

Thanks.

0 Replies