Forum Discussion
Helper IIIHow to change source data from local drive to One Drive
Hi Everyone,
I have a report which i published from my Power BI desktop app. My Source data is stored on my local drive.
I have uploaded all my source data to my one drive and now i want to link my data source from local drive to One Drive in Power BI Services without hamperring the existing reports and Dashbords.
Please advice on this.
Thanks in advance.
Kind Regards
Shibsankar Ghosh.
Hi coolshib
I am assuming your source files are Excel files.
You will have to do two things:
- Find the appropriate URL pointing to your file on OneDrive
If it's OneDrive for Business look here
If it's OneDrive Personal see this post - Change the step of your query that connects to the file from something like this
=Excel.Workbook(File.Contents("C:\dummy\file.xlsx"),null,true)to something like this (OneDrive for Business)=Excel.Workbook(Web.Contents("https://XXXXX-my.sharepoint.com/personal/USER_XXXXX_onmicrosoft_com/Documents/folder/file.xlsx"),null,true)or something like this (OneDrive Personal)= Excel.Workbook(Web.Contents("https://onedrive.live.com/download?resid=XXXXXXX&authkey=YYYYYYYYYYY&em=x&app=Excel"), null, true)
The appropriate credentials would be needed for either connection.
Does that help?
Regards,
Owen
- Find the appropriate URL pointing to your file on OneDrive
6 Replies
- OwenAuger
Super User
Hi coolshib
I am assuming your source files are Excel files.
You will have to do two things:
- Find the appropriate URL pointing to your file on OneDrive
If it's OneDrive for Business look here
If it's OneDrive Personal see this post - Change the step of your query that connects to the file from something like this
=Excel.Workbook(File.Contents("C:\dummy\file.xlsx"),null,true)to something like this (OneDrive for Business)=Excel.Workbook(Web.Contents("https://XXXXX-my.sharepoint.com/personal/USER_XXXXX_onmicrosoft_com/Documents/folder/file.xlsx"),null,true)or something like this (OneDrive Personal)= Excel.Workbook(Web.Contents("https://onedrive.live.com/download?resid=XXXXXXX&authkey=YYYYYYYYYYY&em=x&app=Excel"), null, true)
The appropriate credentials would be needed for either connection.
Does that help?
Regards,
Owen
- coolshib
Thank you so much Owen Auger.
Much Appreciated.
Best Regards
Shib
- OwenAuger
Hi Meishka,
It's a while since I've looked at this so I just ran a test myself 🙂
For OneDrive Personal, the filename itself isn't part of the URL.
You have to get the embed code from the OneDrive portal (Click ... next to the file in OneDrive then Embed), and modify the URL so it is in this format
https://onedrive.live.com/download?resid=XXXXXXX&authkey=YYYYYYYYYYY&em=x&app=Excel
For example, here's what i get from one of my OneDrive personal files:
Original URL
https://onedrive.live.com/embed?cid=61303FCA4FBFDAE8&resid=61303FCA4FBFDAE8%211981&authkey=AIpQ2QGi35IT_zk&em=2
Modified URL for Power BI
https://onedrive.live.com/download?resid=61303FCA4FBFDAE8%211981&authkey=AIpQ2QGi35IT_zk&em=x&app=Excel
I don't believe the authkey changes (I could be wrong though).
Power BI's Web connector authenticates with your username and password using "Basic" authentication, so your credentials are stored in the Power BI service after publishing, or on your local machine in Power BI Desktop.
I just tested and I was able to publish, enter the username/password in the Power BI Service dataset settings, then refresh successfully (without gateway).
Regards,
Owen
Good afternoon OwenAuger,
I've the same problem, but then the orther way around. My current file is a CSV saved on a onedrive of an old collegue, i can't reach this with my credentials. I've fot the same file on a local drive.
In the quiery editer - data source settings - i can only change to a URL (see screenshot). I'm not sure how to get the source to my local drive. Are you able to help?
Thanks in advance
- OwenAuger
Hi lpronk
You will have to edit the code in the Advanced Editor.
You will want to change Web.Contents to File.Contents.
There will likely be a step like this:
=Excel.Workbook(Web.Contents("https://XXXXX-my.sharepoint.com/personal/USER_XXXXX_onmicrosoft_com/Documents/folder/file.xlsx"),null,true)which you should change to a step like this:
=Excel.Workbook(File.Contents("C:\dummy\file.xlsx"),null,true)
- Find the appropriate URL pointing to your file on OneDrive
