Power Query specifications and limits while working with Text files

[nangys]

Hi everyone,

I have a problem while working with text files that are bigger than ~300 MB. Specifically, PQ takes a really long time to process the code. Sometimes hours if the text file is bigger than 1 GB. Do you know what could be happening and if this is a normal behavior?

I checked the Microsoft website and found this information about the limits:

Size of data processed by the Engine

Limited to available virtual memory (for 64-bit version) or about 1GB for 32-bit version, if data cannot be fully streamed, such as when sorting the data set locally before filling it

Soft limit of persistent cache. A soft limit can be exceeded for periods of time.	4GB
Individual entries in the cache	1GB

I have a:

Intel i(7) 2.60GHZ
RAM Memory of 16 GB
Running on 64 bit
W7
Excel 2010
PQ Version: Version: 2.26.4128.242

Additionally, I just noticed that on Query Options -> Data Cache Management Options. Does this has to do with the problem in the case the cache is already using a lot of memory?

Thank you

Regards,

Fernando

 

[Ken Puls]

Hey Fernando,

What is the "bitness" of your Office install? I'm guessing that it's 32 bit, even though you're on 64bit windows. If so, then your cache is limited to the 1GB number above, no matter how much RAM you have in your machine.

Some optimizations you can do under Power Query --> Options:
-Global: Use Fast Data Load
-Current Workbook: Under Data Load turn off the "Allow data preview to download in the background"
-Under Privacy: Use the "Ignore Privacy Levels"

Also, in your query, try to remove as many columns and filter out as many rows as you can early in the query. This will limit the amount of data Power Query needs to apply it's transformations to.

 

[nangys]

Hi Ken,

thanks for the answer.

Office is also running on 64 bit.

I will use of the other optimizations settings that you have suggested. Thank you!