locked
Unable to refresh - The workbook was created in a newer version of Excel!!? RRS feed

  • Question

  • Hi 

    I created a datamodel using Excel 2016 with the built in Get Data (power query) and Power Pivot functions. 

    When my colleagues try to refresh the connections in Excel 2013 they get an error message telling them that the file cant refresh since it was created in a newer version of Excel. 

    My colleagues have the newest version of Power Query installed, even never than the one incorporated into Excel 2016. 

    IS THIS FOR REAL? Is there a workaround for this? According to what i've seen of statements from Microsoft Excel 2016 files should be backwards compatible with Excel 2013. 

    Regards

    Remi 

    Wednesday, October 7, 2015 12:33 PM

Answers

  • The error is not in Power Query versions, but in Power Pivot versions. There have been significant updates to Power Pivot and the Tabular engine backing it between Excel 2013 and Excel 2016. Versions of Power Pivot have always been backward incompatible. Your colleagues should be able to interact with any Power Pivot backed tables, charts, Power Views that you create with 2016 in 2013, but they will be unable to refresh or modify the data model.

    If your colleagues must have access to the data model to make changes, you will all have to use the same version of Power Pivot. If your colleagues just need to consume data from Power Pivot, you are better served hosting the workbook on SharePoint, or on a Power BI site. It is considered a poor practice to distribute workbooks for consumption only.

    If you need to distribute offline files and cannot guarantee all users have the same version of Excel/Power Pivot, your best offline option is Power BI Desktop Pro - a free, standalone program that incorporates Power Query, Power Pivot, and Power View with no dependency on your Office installation.

    GNet Group BI Consultant

    • Marked as answer by Remi Øvstebø Thursday, October 8, 2015 6:20 AM
    Wednesday, October 7, 2015 2:20 PM

All replies

  • The error is not in Power Query versions, but in Power Pivot versions. There have been significant updates to Power Pivot and the Tabular engine backing it between Excel 2013 and Excel 2016. Versions of Power Pivot have always been backward incompatible. Your colleagues should be able to interact with any Power Pivot backed tables, charts, Power Views that you create with 2016 in 2013, but they will be unable to refresh or modify the data model.

    If your colleagues must have access to the data model to make changes, you will all have to use the same version of Power Pivot. If your colleagues just need to consume data from Power Pivot, you are better served hosting the workbook on SharePoint, or on a Power BI site. It is considered a poor practice to distribute workbooks for consumption only.

    If you need to distribute offline files and cannot guarantee all users have the same version of Excel/Power Pivot, your best offline option is Power BI Desktop Pro - a free, standalone program that incorporates Power Query, Power Pivot, and Power View with no dependency on your Office installation.

    GNet Group BI Consultant

    • Marked as answer by Remi Øvstebø Thursday, October 8, 2015 6:20 AM
    Wednesday, October 7, 2015 2:20 PM
  • Thank you for a good explanation! :) 

    I guess I have to update my colleagues as well. 

    I developed a tool to track and ensure that paid maternity leave is conducted in accordance with Norwegian law and that we receive the money that we are entitled to from the goverment. The input of data is being conducted in the sheet itself to avoid having 1 input workbook and 1 report workbook on PowerBI (which would have solved the refresh problem).. It also collects data from other internal systems. 

    I really like using Power Query / Power Pivot to develop administrational tools for the business, its quick , powerful (dax), "bug-free" and easy to learn the users. But its frustrating that Power Pivot always needs the newest version, been this way back to Gemini. Hope they have "standardized" it a bit in excel 2016. 

    Thursday, October 8, 2015 6:19 AM
  • Power Pivot is simply a private instance of SSAS Tabular running behind the Excel process. I doubt we'll get to a point where major version releases in SSAS don't have some level of backward incompatibility.

    Especially currently, Microsoft is still making significant changes in the formula engine which result in large performance improvements.

    Again, if you're distributing solutions to multiple people, the best practice is to host it on a central platform, which also eliminates the need to keep Excel versions in sync.

    GNet Group BI Consultant

    Thursday, October 8, 2015 4:18 PM