Redirect Pivot Table from Local Dataset to Power BI Service

[Eric Glover]

How can I change data sources for Pivot Tables tied to the local dataset that was built with PowerPivot & PowerQuery to the new replacement data model that we built in PowerBI Desktop and published to the Power BI Service?

I have a very large Excel workbook with dozens of Pivot Tables that we use for daily sales reporting. The data model has numerous tables with calculated columns and measures that have been built over time. Many worksheets have multiple slicers so the workbook is huge (200MB) and takes forever to refresh.  

We have replicated the data model in Power BI Desktop and published it to Power BI where it now refreshes daily without a hitch.

So, how do I redirect the data source links in my Pivot Tables to the new Power BI service instead of the local dataset? I can do it manually PT by PT but that will take forever.  I'm hoping someone in the Excel community has an idea how to automate this process.

[#2]

Hi Eric,

I don't know how to change all connections automatically, just intresting why did you use Power BI Desktop to publish the data model on Power BI Serrvices? I'd use the same Excel file to connect the workspace dataset.

[#3]

Sergei, thank you for the quick reply.

We used Power BI simply because we didn't know it was possible to publish the dataset directly from Excel.  But, even if we had published it from Excel, wouldn't we have the same issue?  All the Pivot Tables still point to the local dataset.  How do we point them to the Power BI service?

Or alternatively, is there a way to get the Excel data model to refresh automatically?  If we launch Excel and Refresh All the spreadhseet becomes unusable for 2 hours and locks up Excel.  So, we were trying to let the refresh happen in Power Bi and keep the Excel file skinny by referring to the cloud.

If there is a better way we would love to know about it.

Thanks!

[#4]

Eric,

Power BI Desktop is only not needed extra step which complicates the maintenanace if you'd like to change the model from time to time.

As for the refresh - not sure. With Power BI scheduled refresh you will update your data model in background. But it won't refresh Pivot Tables /Charts connected to your data model. You'll need Refresh All in any case, but another connections will be refreshed.