Uploading Excel connected to PowerBI Data Model to Power BI and refreshing it

[DataMuncher10]

Hi everyone,

We're in early stages of implementing power BI in our company.

We're trying to understand the available options for users to have access to more details about the data they’re seeing in the Dashboards/Reports available in the PowerBI service portal and we know about the following:

• Export Data (xlsx / csv) – exports the filtered recordset
• Analyze in Excel – opens an empty file with a readily available connection to the PowerBI Data Model but no report
• See Records (Right click on a visual’s part)

All these options provide us with some flexibility but it is still not enough. Currently, the users I’m working with, would constantly need export data to excel and re-build the same pivot tables from scratch every time they need to drill into the details.

I know that, by using the PowerBi publisher for excel, it is possible to create a connection from an excel file to the PowerBI data model, create some Pivot Tables/Charts etc. But, if possible, what we would like to have is a “centralized” solution, rather than have users jumping from PowerBI to excel in either local or sharepoint folders.

So my questions are (and please note I am using DirectQuery, with a SQL DB created for Datawarehousing purposes only):

1) Is it possible to upload to Power BI service, an excel file containing a connection to its datamodel and pivot tables and charts along with it?

2) If so, is it possible to refresh (manually and/or automatically) that excel file?

3) If not, are there any alternatives that could help achieve this "centralization and ease of use" goal?

Thank you very much

[v-yuezhe-msft · Accepted solution]

@DataMuncher10,

Based on my test, you are able to upload the Excel to Power BI Service. There are some steps for your reference.
1. Create required Pivot charts in the Excel file.
2. Upload the Excel file to OneDrive for business.
3. In Power BI Service, use "Get Data->Files->OneDrive for Business" entry and use "Connect, manage and view Excel" option to upload Excel to Power BI Service.


4. When you modify data in SQL Server data source, you would need to open the Excel file in your local drive, manually refresh the data in Excel, save the excel file and upload the file again to OneDrive for business to replace the existing excel file. After that, you will find that the Excel wookbook you upload to Power BI Service is refreshed.

Regards,
Lydia

[v-yuezhe-msft · Accepted solution]

@DataMuncher10,

Based on my test, you are able to upload the Excel to Power BI Service. There are some steps for your reference.
1. Create required Pivot charts in the Excel file.
2. Upload the Excel file to OneDrive for business.
3. In Power BI Service, use "Get Data->Files->OneDrive for Business" entry and use "Connect, manage and view Excel" option to upload Excel to Power BI Service.


4. When you modify data in SQL Server data source, you would need to open the Excel file in your local drive, manually refresh the data in Excel, save the excel file and upload the file again to OneDrive for business to replace the existing excel file. After that, you will find that the Excel wookbook you upload to Power BI Service is refreshed.

Regards,
Lydia

[DataMuncher10]

Thanks for the quick reply, Lydia,

The fact that one would have to Open, Refresh, Upload the file again is a considerable downside.

Could you please confirm if that's the only option?

Again thanks

[v-yuezhe-msft · Accepted solution]

@DataMuncher10,

Yes. Otherwise, you can consider to connect to SQL database in Excel directly, then import the Excel file to Power BI Service and set schedule refresh for the dataset.

Regards,
Lydia