Changing PivotTable Names in Excel 2013 + Bug Alert
Reposted from Jason Thomas blog with the author's permission.
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.
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) 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.
However, there is no change to the table names in the underlying PowerPivot data 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
4) You can see that the changes are reflected in the PivotTable field list now.
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!
 | Jason has been working with Microsoft BI tools since he joined the IT industry in 2006. Since then he has worked across SSAS, SSRS and SSIS for a large number of clients like Unilever, Imperial Tobacco, IATA, Schneider Electric, etc. He is currently working at MindTree Ltd, Bangalore (www.mindtree.com) and his personal blog site can be found at http://www.sqljason.com His blogs can also be found at the popular technical site BeyondRelational.com at http://beyondrelational.com/blogs/jason |