In the most recent versions of Excel we’re able to develop some fantastic BI solutions using the Power add-ins for Excel; and some of these add-ins allow us to do the same in Excel 2010.
We recently developed a Power Query and Power Power Pivot based solution for a client in Bradford (UK). The data itself resided in SharePoint lists (SharePoint 2010) and we used Power Query to connect to these lists and import the data into Excel tables.
We then added these Excel tables to a Power Pivot data model, therefore creating linked tables in the data model; and added additional calculations and formulas to the data model using DAX (similar to Excel formulas but act more like SQL queries).
Finally we created some pivot tables and charts over the data model to present the data in report form. Fantastic! EXCEPT!
When the Refresh icon in Excel was pressed the Power Queries refreshed fine, and this could be confirmed by checking the Excel tables and the Power Query info panel.
However; although it appeared as though the Power Pivot data model, pivot tables and pivot charts had refreshed also, they hadn’t! – the data in these didn’t reflect the data in the refreshed Excel tables.
On further investigation, this is a short coming in Excel 2010, where the Refresh icon doesn’t refresh all parts of the solution (primarily the linked tables in the data model and the reports).
Our research led us to some long and complex workarounds to this, which we had little confidence in pursuing, but we were confident there was a better simpler way. And the good new is we found it!
We created a VBA Macro and linked it to a Button on the first report worksheet. Clicking the button gave users the full Refresh functionality that we see in more recent versions of Excel. The VBA Macro, with just one click of the mouse, refreshed the Power Queries, the linked tables in the data model and all of the pivot charts and tables in the report.
The Macro also presented the user with various choices on what to refresh and presented the user with confirmation messages along the way. It also overcame some other little niggles such as the Num Lck key turning itself off.
The VBA Macro that we developed is of some commercial value we believe so we don’t want to show the code here; but if you’re experiencing this issue, and want to overcome it, then please do get in touch with us and we’d be happy to discuss options with you.