Pages

Friday, November 23, 2012

Changing PivotTable Names in Excel 2013 + Bug Alert

Today is Black Friday here in the US and I don’t have time to write a regular post (as I am scourging for deals online and planning to get out for shopping as soon as the insane lines outside the stores dwindle in numbers). So this is just going to be a quick tip for those who have tried upgrading their PowerPivot models with linked tables from Excel 2010 to Excel 2013 and found out that their PivotTable names don’t match anymore.

1 Changing PivotTable names in Excel 2013

For those who are not familiar, let me give an account of the issue. If you upgrade a PowerPivot model which has linked tables from Excel 2010 to 2013, you will notice that the table names in the field list don’t match the table names in your model. As per my testing, this doesn’t break the measures that you might have created using the old table names (as the measures still use the table names in the data model and this name change looks to be more of a front-end display name) but might be a real inconvenience. Follow the steps below to reproduce and solve the issue:-

1) For the purpose of this demo, let us use the PowerPivot file that I created in Excel 2010 for my last post - Measure Selection using Slicers in PowerPivot. You can download it from this link. If you open it in Excel 2010, you can see the original table names in the field list as well as the linked tables used to create the model above.

2 Table names in Excel 2010

2) Now close the file and then re-open in Excel 2013. Julie Koesmarno (blog | twitter) has put a step by step post to upgrade this workbook from 2010 to 2013 here. Also have a look at this post – Upgrade PowerPivot Data Models to 2013 for any potential issues while upgrading your workbook. Now once you have done that, you will see that all the table names have changed in the field list.

3 Changed table names in Excel 2013

However, there is no change to the table names in the underlying PowerPivot data model.

4 Name in PowerPivot model

3) To change the name in the PivotTable field list, all you have to do is to select a cell in the respective linked table, click on Design and then enter the original / required name in the Table Name cell as shown below

5 Rename table 

4) You can see that the changes are reflected in the PivotTable field list now.

6 Name change in Excel 2013

You could avoid doing these steps in Excel 2013 if you had renamed your linked tables in Excel 2010 using the same way before upgrading. If not, it will just take the name of the linked table when you upgrade.

Apparently, this is a known issue and will be fixed in the next release as per this thread.

BUG ALERT!!!

If you paid close attention to the file that you just upgraded, you can notice that the slicer for measure name is not working. Now this seems to work for people who have installed the Office Professional Plus (v 15.0.4420.1017) but for people like me who have installed the Excel Preview (v 15.0.4128.1025), the slicers don’t work. Turns out the reason was that FORMAT function doesn’t play well within a measure expression. All you have to do is to change the measure expression for MsrValue from

MsrValue:=switch (TRUE,
Min ( Msr[MsrId] ) = 1,
FORMAT ( Sum ( Fct[Sales] ), "$#,#0" ),
Min ( Msr[MsrId] ) = 2,
format ( Sum ( Fct[Quantity] ), "#,#0" ),
Min ( Msr[MsrId] ) = 3,
format ( Sum ( Fct[Profit] ), "$#,#0" )
)

to

MsrValue:=switch (TRUE,
Min ( Msr[MsrId] ) = 1,
Sum ( Fct[Sales] ),
Min ( Msr[MsrId] ) = 2,
Sum ( Fct[Quantity] ),
Min ( Msr[MsrId] ) = 3,
Sum ( Fct[Profit] )
)

Of course this does mean that you lose the formatting. Now you can see that the slicers are working. This clearly is a bug as it works in Excel 2010 (as well as in the Professional Plus edition of Excel 2013). I have raised a Connect issue here, please vote for it so that it can be rectified in the next release. Ok, time to run for the Black Friday shopping now!

2 comments:

  1. I am not sure what future fix the thread (RTM Bug of PowerPivot on Excel 2013 64) was referring to. But the described behavior was an intentional design decision made by the Excel team. The underlying rationale was that an Excel table can be used in more places than just as a PowerPivot linked table, therefore users should see a consistent name (the name of the Excel table) throughout Excel. If PowerPivot renames an Excel linked table, the new name is only visible inside PowerPivot, but not inside Excel. This can be very confusing to users of both Excel and PowerPivot, so users should stop renaming linked tables in PowerPivot, instead they should rename them directly inside Excel and let PowerPivot pick up the Excel table name.

    ReplyDelete
    Replies
    1. Agreed Jeffrey, but this can be really confusing for people who are upgrading their PowerPivot models from Excel 2010 to 2013 and are currently not renaming the linked tables in 2010. In the field list for 2010, we still see the name that we gave in the PowerPivot model (and not the linked table name) but suddenly this changes to the linked table name in the field list for 2013. If this is not going to be fixed, I guess we should always follow the rule to rename the linked tables before importing to PowerPivot data model.

      Delete