11-04-2016 12:43 PM
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,
Solved! Go to Solution.
11-04-2016 01:16 PM
11-04-2016 01:06 PM
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.
11-04-2016 01:15 PM
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).
11-04-2016 01:16 PM
11-04-2016 02:02 PM
@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!!
11-04-2016 02:11 PM
Is you primary question:
- Is it possible for Power BI refresh it automatically when data in the Excel files saved to OneDrive / SharePoint is saved?
- I don't beleive the Power BI Service has a way of knowing the data in the Excel file has changed.
- You would have to use the Scheduled Refresh feature for this.
- 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?
- Or is it: Is it possible to Change the Report Connection?
- 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