Refreshing data through Onedrive

[Anonymous]

Hi All, 

This is the first time i am doing this in PowerBI.

I have created a PBIX which has some reports. The data source used is an excel file on my local system. Now this data will be updated everyday and a new file will be available which should be used to refresh the report, what i need is that i instead of me refreshing PBIX everyday manually,the updated excel file will be uploaded on OneDrive for Business everyday and hence it should refresh automatically. I have read many different solutions and blogs however for some  reason i am not able to get through this. 

Can someone show me step by step how to do it. I hope i dont have to create the reports all over again.  😞

these are the info i have gone through, but not able understand them all.

https://community.powerbi.com/t5/Integrations-with-Files-and/Does-Auto-Refresh-from-Excel-on-Sharepo...

https://powerbi.microsoft.com/en-us/documentation/powerbi-refresh-excel-file-onedrive/#options-for-c...

https://powerbi.microsoft.com/en-us/documentation/powerbi-refresh-data/

[danielhunter · Accepted solution]

Local Excel

Source = Excel.Workbook(File.Contents("C:\Users\xxxxxxxxxxxxxxxxxxxxxx\Device-Order-Data-CDN.xlsx"), null, true),

Onedrive

Source = Excel.Workbook(Web.Contents("https://xxxxxxxxxxxxxxxxxxxxxxxxxxxxx/Documents/Device-Order-Data-CDN.xlsx"), null, true),

[v-huizhn-msft]

Hi @Anonymous,

First, you get data from one excel file stored in OneDrive, I test it using OneDrive for personal. Please review the detailed steps about how to get data from from an Excel file stored in OneDrive for Personal here.

Second, create a report in Power BI desktop, and publish it to service.

Third, you will see the dataset in Power BI service, right click->refresh schedule-> you will get the following interface.



Please turn on the "Keep your data to up to date", and choose refresh daily, please notice the highlighted in the screenshot above.

Best Regards,
Angelia

[Anonymous]

@v-huizhn-msft i have already created the report on PBI desktop, cannot afford to recreate it... moreover i am getting totally different options ...

Now i am trying change the source of the file from my local machine to the OneDrive.

Below is what i did...

1. Uploaded the excel file i used in my report which is developed on my laptop.
2. Right  Click on the file >Open in Excel>File>Info>Under Info is the name of my file >Click on Test>Click on Copy Link to Clipboard

Click on Test>Click on Copy Link to Clipboard

3. In PBI desktop Edit Queries>Data Source Settings>Data source in current File>Select the file to replace the source with>Change Source>Basic(the header of the dialouge box says "Excel" since the original file was an excel on local machine and under the File Path box change the "open file as" from "Excel" to "Html page"> Paste the link copied (remove"web=1" as suggested in this document [ https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-use-onedrive-business-links/ ] )> click "ok"> Select"Organisational account> Enter credential to sign in.



 > Click ok > Click "Apply Changes" Then i get this error message

I hope i have explain it throughly :).

[danielhunter · Accepted solution]

Local Excel

Source = Excel.Workbook(File.Contents("C:\Users\xxxxxxxxxxxxxxxxxxxxxx\Device-Order-Data-CDN.xlsx"), null, true),

Onedrive

Source = Excel.Workbook(Web.Contents("https://xxxxxxxxxxxxxxxxxxxxxxxxxxxxx/Documents/Device-Order-Data-CDN.xlsx"), null, true),

[Anonymous · Accepted solution]

 Dear   @danielhunter i have been able to resolve this... for some wiered reason the problem was with the Link i was getting from the file... It was completely different than what you showed me.
Today again i gave it a try to change the source from excel file on my local machine to Excel on One Drive. 
I uploaded the file on OneDrive opened them in Excel ( not excel Online) the difference i noticed this time before i could copy the link was that it gave a message that the file is opened in "Offiline Mode" .
Then copied the link from the file. In PBI went to "Data source settings" and changed the link (removed web=1) from the end saved and published on Service.


I also tested it...

Method was :

Deleted the file from Onedrive.
I had the original file on my Laptop. ( Name of the file is same)
Deleted some rows from the file and saved it.
Uploaded this file on Ondrive.
Refreshed data manually after 5 mins.
Data and visuals are updated with change.


Root of the issue was link and i dont know why 😄 .