Forum Discussion
Using BI Desktop with Excel File in OneDrive for Business
I think I understand that, but if I start in the Power BI Desktop designer and then publish to the service, then I will always result in a data source that is considered on-prem. Our University doesn't currently provide a way for our deaprtment to pay for a pro account, so I was hoping I could still use the desktop designer with a source in OneDrive for Business. I'll keep trying.
rjhale This is supported in PowerBI desktop, although you have to do a few things manually.
1. Get the URL to the file.
Files in OneDrive have public URLs that goes directly to them. For example:
https://mycompany.sharepoint.com/personal/pqian/Documents/Test.xlsx
2. In PowerBI desktop, click "From Web"...Paste in the URL
3. When asked for credentials, click "Organizational Account", then "Sign in"
Now you can operate directly on your OneDrive file.
When you published the report, in order to schedule refresh, you must sign in again on PowerBI.com since we don't transport credentials. Go to your data set settings, and "Edit Credentials", select "OAuth2", sign in there.
This is interesting. I followed your steps and was able to successfully connect PowerBI Desktop to my Excel file in OneDrive for Business, however; I'm seeing something odd now. When I make the connection it provides me options to import any of the worksheets or the named ranges, but it does not actually bring in the data model. The data model is the important part as it contains all of the data and my custom measures. This is what it looks like in BI Desktop after I import all of the objects in the workbook:
rjhale That's right. PowerBI desktop has its own Data Model and you need to build it like Excel. If you were using PowerPivot and PowerView before, you are looking to Import From Excel (File->Import).
What is your original data source?
It seems that I can't use the file -> import feature for a file that exists in my OneDrive for Business space. I did try using the file -> import function and pointed it at the file using the OneDrive for Business client, however; when I published this file the refresh was disbled on the data source.
My original data source was a Microsoft Access table which I loaded into a PowerPivot data model. That data model is part of the Excel file that is sitting in my OneDrive for Business space.