In this blog entry I'm going to discuss accessing an Excel file that's stored in OneDrive. What I do *not* want to do is associate the files to my local file path such as c:\users\melissa\onedrive... because another person who downloads the workbook and wants to edit or look at the Power Query settings will get an error. So I want to use the URL for OneDrive to make it easier to pass this workbook among other people. This took a bit of trial and error to find the right URL that would work with Power Query.
Storing the Source File in OneDrive
The first thing we want to do is get a file in OneDrive and make sure it's shared. For this purpose I'm just using a little simple file called PowerQueryTest, directly in my Public OneDrive folder. However, this same logic can be applied to a file shared with a set of coworkers.
Getting a Link for the Source File
If you click the Excel file to view or edit it in the browser, the URL looks like this:
Power Query doesn't like that URL. So, the next thing you might think to try is to generating a link with the Share feature. For my file, that URL looks like:
The 2nd URL is a little different from the first one, but the resid that identifies the report is the same so that's good. But, the above format still won't work in Power Query.
What I've found that will work with Power Query is this kind of URL structure:
Basically, it's https://onedrive.live.com/download?&resid= followed by the unique report # which you can obtain from the URL as you view or edit a report in the browser.
Using the URL in Power Query
Even though we're going after contents of an Excel File, we want to use the From Web menu item on the Power Query ribbon (instead of From File). Paste in the URL in the correct format mentioned just above.
In the Navigator menu on the right pane, select the sheet and then Edit.
Now you can carry on with whatever steps you need for your file. For my teensy little set of sample data here, I told it to "Use First Row as Headers" and then gave it a name and description in the query properties.
Credentials to Access to One Drive from Power Query
Now, the next thing you need to know is what will happen when the next person opens this workbook and wants to edit or even just look at how the Power Query steps. So, for this last series of screen shots, we are seeing it from the perspective of person #2 who just opened the file.
The first thing person #2 sees is the normal security warning. It's ok to click Enable Content since it's been shared by our trusty coworker.
When we hover over the query name in the Workbook Queries pane, we see a warning in the popup box. Permission Error: Credentials are required to connect to the Web source. That makes sense, even though this was a file I had in the public share - Power Query just doesn't know it yet.
Go ahead and click Edit.
Then choose Edit Credentials.
Lastly, select Anonymous access. For me, the first radio button worked perfectly.
You might want to let your coworkers know to go ahead and choose anonymous credentials if they see the warning and want to edit, view, or refresh Power Query. (Of course, if it's not anonymous then let them know which organizational account to sign in with instead.)
Power Query Saved Data Source Settings
On the Power Query ribbon, there's a Data Source Settings item in the Machine Settings section. After person #2 set their credentials in the previous step, their machine now stores this setting for OneDrive.
That's it! Found a different way? Leave a note in the comments.
As always, Power BI is constantly evolving and changing. This information is correct as of Aug. 2014.