I moved a PowerPivot workbook to the cloud (on OneDrive for Business) for use in the Power BI service. After importing the workbook in the Power BI service, I wanted to schedule a refresh. As I had already setup the personal gateway for access to on-premise data sources from the cloud (see my blog post about installing the personal gateway), I was quite confident that everything would work just fine. My PowerPivot workbook connects to a database and an Excel workbook with custom data.
Unfortunately, Excel workbooks are not (yet) supported as an on-premise data source, so the setup of my scheduled refresh failed. The solution seemed simple: move the Excel workbook with the custom data to OneDrive for Business as well and change the PowerPivot connection to read the OneDrive for Business workbook instead of the local workbook.
Again I was out of luck as it is not possible to change a PowerPivot connection to read a OneDrive for Business workbook. The only solution I found is to read the OneDrive for Business workbook with Power Query and let Power Query load the data to the data model. Be aware that, because you will re-create the PowerPivot table, you will have to re-create all the relationships, hierarchies, calculated columns and measures in the PowerPivot table based on the Excel workbook data!
If you plan on creating a PowerPivot workbook for use in the cloud, and you have PowerPivot tables loaded from on-premise files like other Excel workbooks, then always load the data from the on-premise files to PowerPivot using Power Query.
So, let’s have a look a the steps to load the PowerPivot table using Power Query instead of with a direct connection:
Step 1: make a backup of the PowerPivot table/data model
As mentioned before, you will have to recreate relationships, hierarchies, calculated columns and measures for the PowerPivot table you are about to remove and recreate with Power Query.
Step 2: remove the PowerPivot table
You must remove the PowerPivot table from the data model. If you do not remove the PowerPivot table, Power Query will not replace the PowerPivot table but will create a new table instead with the name of query followed by a ‘1’.
Step 3: create the Power Query query to load the table to the PowerPivot data model
From the ‘Power Query’ pane, select ‘From File’ and ‘From Excel’
In the ‘Browse’ dialog window, paste the URL for the OneDrive for Business workbook in the ‘File name’ and click ‘Open’. You can copy the URL from OneDrive for Business. (when you paste in the ‘File name’, delete everything after ‘xlsx’).
If it is the first time that you access the OneDrive for Business file, you will be asked to provide credentials to connect to the file. Provide your Office 365 account that has access to the OneDrive for Business file. You do this by clicking the ‘Sign in’ button in the ‘Organizational account’ section. Then you can click ‘Connect’ to access the file.
In the Navigator you can now select the worksheet you want to load to the PowerPivot data model.
You can now load the data directly to the data model or you can choose to edit the query. Click ‘Edit’ to open the Power Query query editor where you can customize your query and provide the name for your target table in PowerPivot.
When you are done customizing the query, the data can be loaded to the PowerPivot data model.
Step 4: apply changes to the new PowerPivot table
You can now recreate the relationships, hierarchies, calculated columns and measures for the PowerPivot table to reflect the original situation.