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

Refreshing data through Onedrive

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/

2 ACCEPTED SOLUTIONS

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

 

View solution in original post

Anonymous
Not applicable

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

View solution in original post

4 REPLIES 4
v-huizhn-msft
Employee
Employee

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.

1.PNG

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

@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 ClipboardClick 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.

Org.png

 > Click ok > Click "Apply Changes" Then i get this error message
Error CC.png

I hope i have explain it throughly :).

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

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

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