Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
coolshib
Helper III
Helper III

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

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

6 REPLIES 6
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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

lpronk_0-1706878295068.png

 

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)

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

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

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:

OwenAuger_2-1649835551526.png

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.

OwenAuger_1-1649835359251.png

 

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Thank you so much Owen Auger.

Much Appreciated.

Best Regards

Shib

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors