Results 1 to 3 of 3

Thread: Power Query specifications and limits while working with Text files

  1. #1
    Seeker nangys's Avatar
    Join Date
    Jan 2017
    Posts
    11
    Articles
    0
    Excel Version
    2010

    Power Query specifications and limits while working with Text files



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,401
    Articles
    45
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Seeker nangys's Avatar
    Join Date
    Jan 2017
    Posts
    11
    Articles
    0
    Excel Version
    2010
    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!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •