Reply
Regular Visitor
Posts: 18
Registered: ‎07-13-2015

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:

 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/6935840-allow-power-bi-designer-t...

 

Super Contributor
Posts: 1,350
Registered: ‎07-03-2015

Re: Using BI Desktop with Excel File in OneDrive for Business

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. 

Regular Visitor
Posts: 18
Registered: ‎07-13-2015

Re: 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. 

Highlighted
Senior Member
Posts: 252
Registered: ‎12-07-2015

Re: Using BI Desktop with Excel File in OneDrive for Business

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

Super Contributor
Posts: 1,350
Registered: ‎07-03-2015

Re: Using BI Desktop with Excel File in OneDrive for Business

Regular Visitor
Posts: 18
Registered: ‎07-13-2015

Re: Using BI Desktop with Excel File in OneDrive for Business

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:

 

Excel-BIDesktop.png

Senior Member
Posts: 252
Registered: ‎12-07-2015

Re: Using BI Desktop with Excel File in OneDrive for Business

@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?

Regular Visitor
Posts: 18
Registered: ‎07-13-2015

Re: Using BI Desktop with Excel File in OneDrive for Business

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.  

Senior Member
Posts: 252
Registered: ‎12-07-2015

Re: Using BI Desktop with Excel File in OneDrive for Business

I think your best course of action to have a refreshable data set published from PBI desktop is the following setup:

AccessDB (local) <- Personal Gateway <- PowerBI.com
 ^
 +------- PowerBI Desktop

I haven't verified this before, maybe this will also work:

 

AccessDB (OneDrive) <- PowerBI.com
 ^
 |------- PowerBI Desktop

In other words, put the data source directly to OneDrive and connect to that. You don't really need Excel in the picture.

 

For the calculations that you already defined in your existing Excel Data model, you need to migrate them over by importing or rewriting

 

Regular Visitor
Posts: 18
Registered: ‎07-13-2015

Re: Using BI Desktop with Excel File in OneDrive for Business

The first option probably isn't the best option for us using the data gateway requires a Pro account. We're trying to avoid that for the moment because it will likely be a while before we have an outlet for purchasing accounts.

 

I like your second suggestion, and I've actually setup a test to do just that. I'm running into a new problem though. I put the Access DB on ODB, connected to it using the Web functionality in BI Desktop, published the project to the BI service, setup the oAuth2 authentication in the service.  The data and reports come over correctly, but when I choose Refresh Now I get the following error:

 

Can't connect to the data source.

PersonalGateway_DataSourceKindLabel
Data source errorMicrosoft Access: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 64-bit version of the Access Database Engine 2010 Access Database Engine OLEDB provider is required to connect to read this type of file. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987.
Cluster URIWABI-US-NORTH-CENTRAL-redirect.analysis.windows.net
Activity ID8921653f-0461-2c4c-8023-07eb28e5bfd9
Request ID6c12b369-ed47-3457-7c71-9d7fe703edac
Time2016-02-12 13:23:55Z

 

The Access DB in question was created with a 64-Bit version of Office 2013 x64 on Windows 7 x64 and published with BI Desktop x64.  I've also tried this in the other direction and used all 32-Bit applications on the Windows 7 x64 and Windows 10 x64.  I understand that bitness matters in this case, so I've tried to be as consistent as possible when creating and publishing, but I continue to get this error.  I've seen some other posts that suggest installing the Office 2010 x64 Access components, but even that did not fix my problem.

 

Any ideas why I continue to receive this error?