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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
andresgeb
Regular Visitor

Change Report Connection

Hi everyone: I have a report created with Power BI Desktop that consumes an Excel file that is hosted on my PC. 

When publishing to PowerBI service, I realize that the excel connection wants to install a Personal Gateway in my PC to connect to the excel file..., but I don't want to do that, because my client wants that excel file in the cloud (Sharepoint On-line / One Drive for business)...

 

So, I recently uploaded that excel file to a library on SharePoint On Line and shared with everyone in my organization. Then, I created a datasource in Power Bi Service that points to that file in Sharepoint... 

Now I want to connect that new datasource to my report, so when a user updates that file in sharepoint, the report updated automatically.. 

Is that posible?

 

Thanks in advance,

Regards.

1 ACCEPTED SOLUTION

@andresgeb@alanhodgson This blog is a good walk through of how to use an Excel file as a source in SharePoint by @Mike_Carlo. Once you publish to the Service you can configure the refresh and this does not require a gateway.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

5 REPLIES 5
ascendbi
Advocate II
Advocate II

Hi @andresgeb,

 

Is you primary question:

  1. Is it possible for Power BI refresh it automatically when data in the Excel files saved to OneDrive / SharePoint is saved?
    1. I don't beleive the Power BI Service has a way of knowing the data in the Excel file has changed.
    2. You would have to use the Scheduled Refresh feature for this.
    3. Note that if you are using power BI to Consume you Ecel Power Pivot Data Model (not .bpix) via Power BI Excel Online, there is a OneDrive refreash feature. But that means your Data Model and your Data are in the Same File. This may not be ideal or desired?
  2. Or is it: Is it possible to Change the Report Connection?
    1. Yes, the connection can be changed. We usually create a new Query and once connected, copy and paste from new query to old the top rows of connection / source data using the Advanced Editor window

 

 

David

@andresgeb@alanhodgson This blog is a good walk through of how to use an Excel file as a source in SharePoint by @Mike_Carlo. Once you publish to the Service you can configure the refresh and this does not require a gateway.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

@Seth_C_Bauer You saved my life, that worked perfect!

After a few data tranformations on the query editor I finnally connected to the file in the cloud, and the refresh was good!.

Thanks a lot!!

 

Regards.

alanhodgson
Solution Supplier
Solution Supplier

Hey @andresgeb,

 

I am currently running an Excel spreadsheet from my organizations OneDrive for business to a dataset in PBI Service, and the only way to refresh the data in the PBI Service (that I have found) is to refresh the Excel spreadsheet (manually, by opening and hitting "Refresh All"). If you do not have PBI set up for a scheduled refresh, then go to the dataset in PBI and refresh now.

 

Essentially, PBI Service has no way of communicating through Excel to your data source.

Thanks @alanhodgson for your reply!.

My Excel File don't have any external connections, there are only plain tables.

My problem is that in my recently published report , I cannot change the connection to the datasource that connects to that file but in the cloud... I want my report connected to the excel file in the cloud (without gateways to my PC).

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors