none
Power Query unstable with Excel 2010 ? – "Errors were detected…" RRS feed

  • Question

  • I recently changed of company, and unfortunatly the new one still uses MS Office 2010.C

    Strong user of Power Query, I use it to bring my data into PowerPivot (through a direct connection or sometimes loading trough a table in Excel).

    After refreshing Power Query, I update PowerPivot and then refresh several PivotTables. During this process I really often experience a lagging laptop. Excel runs an infinity time the OLAP  calculation… And then if I try to save the file, I have an horrible error message saying “Errors were detected while saving file in Excel 2010″. Sometimes, if I wait for some minutes, I can finally save the  file.  Otherwise I manually shut down Excel (Ctrl+Alt+Supr), open it again, and magically, everything works fine (refreshing, updating & saving) until the next time I restart the refreshing process. Every time"Microsoft.Mashup.Container.NetFX40" seems where the pain comes from (?).  

    You may agree with me: it’s a painful process whereas I never have this with Excel 2013. Especially as, the final users of my file start thinking that PowerPivot & power query are their nightmares as they experience the same kind of issues.

    I also found that if I disable Power Query when working with a file, I can refresh as many as I want PowerPivot without any bugs. Sometimes, the recalculating process is longer than usual, but I close the file as soon as it happens, and then that works well.



    Unfortunately, I don’t know where it can come from :( I try to google it but I don’t find good solutions. I have already upload the laptop to 64-bit, add 16 GB of Memory RAM, still really bad ! 


    Is anybody experienced something like this ?
    Could it be an issue with the network, or our IT system solution ?

    Mu current version of Excel & add-ins:
    Excel version: 14.07166.5000 (32-bit)
    PowerPivot: 11.0.5058.0
    Power Query: 2.29.4217.1861 32-bit



    Thanks,

    Patrick


    Monday, May 9, 2016 6:18 AM

Answers

All replies

  • Hi Patrick,

    I'm not sure why you are able to refresh PowerPivot connections when Power Query is disabled - are those Power Query connections? If so, you should not be able to refresh them when Power Query is disabled. And if they are not Power Query connections, I'm not sure why Power Query would affect them.

    When your laptop is experiencing the slowdown, can you open your Task Manager and take a look at the EXCEL.EXE and Microsoft.Mashup.Container.NetFX40.exe processes, and see how much memory they are consuming and how much CPU activity there are in those processes?

    Can you also try updating to the latest version of Power Query?

    Monday, May 9, 2016 7:05 PM
  •    

    Thanks David for your quick reply !

    I can't upload any pictures because I have this bloody message from social.Thechnet: "Body text cannot contain images or links until we are able to verify your account". I googled it but I haven't found a quick way to fix this :(.

    I will continue the message without picture, I hope it will be still enough clear.

    Sorry if I was not clear. I disable PowerQuery only when I don't refresh my connections in Power Pivot.

    Otherwise, the refreshing process through PowerPivot is painful as Excel crashs several times. I have these kinds of messages when I click on refresh: "Sytem.Outof MemoryException". I use ODBC connection with the connection properties, otherwise linking to table directly into the spreadsheet was even worst than ODBC connection.


    So I refresh one connection by one connection, closing the consolidated Excel spreadsheet between each refresh. Sometimes I even can't save the file as I have randomly this error message: "Errors detected while saving [...]"


    In the Windows Task Manager:
    EXCEL.EXE *32 = 526,264 K
    Microsoft.Mashup.Container.NetFX40.exe = 93,380K
    Microsoft.Mashup.Container.NetFX40.exe = 63,520K
    Physical Memory = 33%


    Every time I updated to a upper version of Power Query I had the same kind of issues. And my IS team is not really helpful as they consider Power Query out of their scope :/


    Would it be an issue with the configuration of our machine ?
    Do you think if I link powerPivot to different Excel spreadsheets where the dataset are sitting. This Dataset being reshaped by PowerQuery initially, and one dataset = one excel file, that could help ? Like that, I avoid using PowerQuery in my consolidated spreadsheet ?

    Thanks again for your precious help. I so need to find a solution otherwise, I will have to drop the great tool Power Query.



    Wednesday, May 11, 2016 1:43 AM
  • If you are getting "System.OutOfMemoryException" you may be hitting the memory limit of 32-bit Excel. Can you try installing the 64-bit version instead?
    Thursday, May 12, 2016 6:56 PM
  • Hi David,

    Thanks for this reply. I work with my IS department on this suggestion and I will tell you if that works.



    Patrick Burger

    Thursday, May 19, 2016 1:10 AM