How to change source data from local drive to One Drive

[coolshib]

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.

[OwenAuger · Accepted solution]

Hi @coolshib

I am assuming your source files are Excel files.

You will have to do two things:

1. 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
2. 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

[lpronk]

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)

[Anonymous]

Hi Owen,

Where do I put the filename on the link for Onedriver Personal case?

Ans is it everytime I change my Onedrive Password, I have to change the Authkey on the link, is it correct?

Thank you for your help.

Meishka

[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

[coolshib]

Thank you so much Owen Auger.

Much Appreciated.

Best Regards

Shib