Change Report Connection

[andresgeb]

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.

[ascendbi]

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

[Seth_C_Bauer · 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.

[andresgeb]

@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]

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.

[andresgeb]

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).