Results 1 to 2 of 2

Thread: Excel 2010 ... Connection Changed from "PowerPivot Data" to "ThisWorkbookDataModel"

  1. #1
    Seeker Ted Murphy's Avatar
    Join Date
    Apr 2016
    Location
    Dublin
    Posts
    11
    Articles
    0

    Excel 2010 ... Connection Changed from "PowerPivot Data" to "ThisWorkbookDataModel"



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

    Earlier this week I had cause to Download Excel 2010 from the MSDN Site and re-install it.

    Suddenly the following Function call ="Fiscal Year: "&CUBERANKEDMEMBER("PowerPivot Data",Slicer_FiscalYear4,1) started returning a #NAME error

    Once I changed to connection to "ThisWorkbookDataModel" e.g ="Fiscal Year: " &CUBERANKEDMEMBER("ThisWorkbookDataModel",Slicer_FiscalYear4,1)
    it returns the following:
    Fiscal Year: 2016

    Apart from having to edit a ton of individual Cell values ... my concern lies with what will happen when I distribute the Workbook to my client ... who will not have updated his copy of Excel 2010.

    Anybody know when this change came about?

    What is the best approach to take?

    Thanks,

    Ted

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,319
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Ted, you hit me with a new one here. I thought that "PowerPivot Data" was 2010, and "ThisWorkbookDataModel" was 2013. You're actually seeing "ThisWorkbookDataModel" working in 2010?

    One method that Bob Phillips suggested in the past was to create a named range (let's call is "MyCube"). MyCube would then be the correct version for the workbook. This, at least, would allow a very easy conversion, maybe even via a drop down list on a control panel worksheet. I'm not sure if I'm a fan of this, but you might even be able to set this up:

    -Create a CUBE formula that pulls something that will never fail, with the ThisWorkbook string.
    -Create an IFERROR statement to test it, and return "PowerPivotData" if it does fail, or ThisWorkbook if not
    -Have the named range point to that cell

    I haven't tested this, and it would add extra overhead, but may solve the issue.
    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.

Posting Permissions

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