locked
Memory Exception while using Power Query for Excel 2010 RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Team,

    I'm using 32 bit power query for 2010 office professional edition. While loading data i'm getting memory out of exception error in both power query and Microsoft .Net Framework. My data is very less and I have loaded around 25 tables(notepad) of data and I have used text files as source. Size of the excel was aroung 1.5 MB when i got this error. My PC configuration is 4gb RAM, Windows 7. Below are the steps i have used after loading data into power query.

    1. Renaming Columns

    2. Trimming Data

    3. Remove null rows

    4. Change Data Type

    5. Merger two tables.

    Kindly Suggest me how I can resolve this error.

    Thanks,

    Nani

    Thursday, May 12, 2016 2:22 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Regarding the out-of-memory error, another thing you might try is disabling background analysis.

    • Open your workbook
    • Click Data->New Query->Query Options
    • Under Data Load, uncheck "Allow data preview to download in the background"

    For changing the source, the best thing to do would be to refactor your queries so that there's one query that defines the source, and all the other queries reference that query. You can do this for one of your queries using the "Extract previous query steps" menu option (see this page for more info...just search it for "extract previous"). The other 79 queries would have to be updated manually, using the Advanced Editor. But once you did this refactoring, updating the source in the future would be easy. You'd only have to change it once, and all 80 queries would then point to the new location.

    Ehren


    Tuesday, May 17, 2016 5:06 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Nani. It depends on exactly what your queries are doing, how the data is being merged, and the size of the source data. Can you send a frown from Power Query and include the info you described above?

    The only other thing I can suggest at the moment would be to use 64-bit Excel/PQ.

    Thanks,

    Ehren

    Thursday, May 12, 2016 6:57 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Ehren,

    Thanks for the reply. It was helpful. However I have another query. Can you please assist me in resolving this.

    I'm having around 80 queries in power query and the source for them is in my drive. Suppose if I want to change the source for all the queries, how can I do for all of them in A single go..?? Is there any option available.

    Tuesday, May 17, 2016 7:30 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Regarding the out-of-memory error, another thing you might try is disabling background analysis.

    • Open your workbook
    • Click Data->New Query->Query Options
    • Under Data Load, uncheck "Allow data preview to download in the background"

    For changing the source, the best thing to do would be to refactor your queries so that there's one query that defines the source, and all the other queries reference that query. You can do this for one of your queries using the "Extract previous query steps" menu option (see this page for more info...just search it for "extract previous"). The other 79 queries would have to be updated manually, using the Advanced Editor. But once you did this refactoring, updating the source in the future would be easy. You'd only have to change it once, and all 80 queries would then point to the new location.

    Ehren


    Tuesday, May 17, 2016 5:06 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Ehren,

    Thanks for the response. I was able to change the source by putting it in a separate query. Is there any other possible way to edit queries other than the Advanced Editor. I would like to understand how the queries are stored internally(In Computer). Is it text file or any other file..??

    What I would like to understand is whether I can extract all my existing queries into a single file and update them and then load them again into power query..??

    Thanks, Nani


    • Edited by Nani_S Thursday, May 19, 2016 4:24 PM
    Thursday, May 19, 2016 4:20 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Nani. One hacky way to extract all the queries is to send a frown (though I don't think this works from Excel 2016). If you check "Include Formulas", the email will contain the source code for all your queries.

    Unfortunately there's currently no equivalent way to load all the queries back in again.

    Ehren

    Thursday, May 19, 2016 5:39 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Ehren,

    I have two reports, each report contains two columns(once Column contains Key and other column contains numeric data). I tried merging these two reports using merge option and then selected left outer join and I was successful. But this involved loading both the reports into power query as two separate queries and then create another query for merge. Is there any other possible way where I can do this process in a single query, i'e in a single query can I load the data from both the reports, reformat them and then merge them into a single table with three columns(Key, Column from the first table, Column from the second table).

    Also I would like to know regarding the career opportunities in power query.  What are the pre requisites and expectation from an interviewer perspective..??

    Thanks, Nani

    Monday, May 23, 2016 4:47 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Nani,

    What's the issue with having the two reports as separate queries? If you don't want them loaded to an Excel sheet or the data model, you can change their load settings to "Connection Only" (right-click on the queries in the Queries Pane and select "Load To..."). This will mean only the final merge query is loaded to Excel.

    Regarding career opportunities in PQ, I'd recommend you contact some of the Microsoft MVPs who are active in the PQ community about this.

    Ehren

    Monday, May 23, 2016 5:54 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    Thank you for your response here.

    The link you have shared above is not landing to a page.

    Can you please provide an updated link.

    Thank you. 

    Monday, August 10, 2020 3:19 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Bilal. Are you referring to the "Extract previous query steps" link? It's still working fine for me. What do you see when you click it?

    Ehren

    Monday, August 10, 2020 6:00 PM