Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

Uploading Excel to PowerBI for use as datasource

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 ?

 

 

1 ACCEPTED SOLUTION
camargos88
Community Champion
Community Champion

@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



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

5 REPLIES 5
mahimabedi
Responsive Resident
Responsive Resident

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.

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
Not applicable

I don't have that option :_o(

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.

10284.jpg

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
Community Champion
Community Champion

@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



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.