Uploading Excel to PowerBI for use as datasource

[Anonymous]

This should be so easy but I can't get it to work. 

I have a number of reports (multiple PBIX files) that were all built in Desktop from the same local Excel workbook, each doing a bit of transformation along the way in Power Query.  These reports are published to an App in PowerBi.

When I want to change the data in the reports I have quite a lengthy process of updating and saving the workbook locally, then opening each of the PBIX files and refreshing the data before re-publishing them all manually.

What I'd like to be able to do is update the Excel workbook once, and all the files that were build from it automatically (ideally) update to use the new data.  This way I could easily swap out different WorkBooks, to show different scenarios etc.

I've tried uploading the workbook to the datasets page in PowerBi, and use that as a source, but there are 2 issues: #1 desktop won't allow me to switch the datasource to a powerBI dataset and #2 when I try to create from scratch (because of #1) using the Excel dataset, I can't transform it.  My interpretation of this is that the datasets are the "finished" article, so this approach won't work.

I'm going round in circles, does anyone have any solutions to this conundrum ?

[mahimabedi]

If you have the option of saving the excle on Sharepoint or Onedrive, then can can set up an automatic refresh in power bi service without any gateway.

[MeadowDec]

What are the steps for achieving this?

Cannot see how you can use a file uploaded to the service as a datasource for a new PBI desktop report/model?

[Anonymous]

I don't have that option :_o(

[v-jingzhang]

Hi @Anonymous 

I suggest installing an on-premises gateway on your local machine so that you can refresh the dataset on Power BI Service easily without going to refresh the data and republish the report again in Power BI desktop. 

There are two types of on-premises gateways, both of them can be used to refresh datasets from local Excel workbooks. And multiple datasets can be mapped to the same data source of a gateway.

Installation guide: https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-install

Refresh configuration guide: https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-sql-tutorial ,  https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-enterprise-manage-scheduled-refresh

Please take note that the guidance documentation takes a SQL Server DB data source for example, so select File as the Data Source Type as below when you add an Excel workbook as data source to the gateway.

Hope this will be helpful.

Best Regards,

Community Support Team _ Jing Zhang

If this post helps, please consider Accept it as the solution to help other members find it.

[camargos88 · Accepted solution]

@Anonymous ,

If you use Gateway, you do not need to open the pbix files and refresh them. 

Only modify the source and save it, the gateway will do the work.

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem