Something that got lost in all the excitement around Excel reports connected to Power BI datasets working in Excel Online is the fact that Excel reports connected to the Excel Data Model – aka Power Pivot – now work too (although I did mention it here). Right now it’s not possible to refresh the data stored in the Excel Data Model if you are connected to external data sources. However, today I noticed something nice: if you are using a, Excel table in the same workbook as a source for a table in the Excel Data Model, if you make a change to the table in the worksheet then it is possible to refresh the data in the Excel Data Model.
Here’s an example. In Excel on the Desktop I created a new workbook and added a simple table to a worksheet:
I then moved to the Power Pivot tab on the ribbon and clicked the “Add to Data Model” button to add this table to the Excel Data Model:
I then created a PivotTable connected to the Excel Data Model:
After saving the workbook to OneDrive for Business, I closed it in Excel Desktop and reopened it in Excel Online in the browser:
Finally I was able to change a value in the source table, click the Refresh Selected Connection button on the Data tab in the ribbon, and see the change reflected in the PivotTable connected to the Excel Data Model:
Of course it would be better if you could refresh external data sources too but I still think this could be useful, for example if you had multiple users updating forecasts or budgets in an Excel table in the browser and were using the Excel Data Model for reporting on this data.
Well – I still find it strange that the Power Query is not yet available in Excel Online. Combining it with Power Automate and Office Scripts would take automation to another level. If there would be Analysis for Office (SAP) Add-in working in Excel Online then we would be in analytics heaven
Be patient!