Forum Discussion
Using BI Desktop with Excel File in OneDrive for Business
We have an Excel file that has a data model derived from a Microsoft Access DB. The Excel file and subsequent data model is updated locally and then synced to OneDrive for Business. We then use the option to import that data into the PowerBI Service, so that it may be refreshed and we can build reports around it.
All of that works fine, and the refresh of the data was a nice surpirse. Now I'm wondering if it is at all possible to then create reports around this data source using the full PowerBI Desktop client? The Desktop client provides more functionality, but as of right now I cannot figure out how I would go about editing or adding a report from the Desktop client with a data source. If I use the OneDrive for Business client, import the Excel file from from OneDrive into the Desktop designer, create some reports, and then publish them to the service it recognizes the files as having on-prem sources. This then breaks the ability to sync unless we have a pro account (which we cannot pay for at the moment).
Any ideas on how we may be able to work around this? I think this feature would alleviate this problem for us, but it may be a long ways off:
9 Replies
This is what I understand - others may know better.
Power BI service needs a source. Your source is an Excel file. Once you go down that path, you can only edit the PBI part in the service. The other approach is to start with PBI Desktop as the source. As you mention, this gives a lot more flexibility.
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.
