Forum Discussion

rjhale's avatar
rjhale
Helper V
10 years ago

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...
Need Help
MattAllington's avatar
MattAllington
Community Champion
10 years ago

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. 

rjhale's avatar
rjhale
Helper V
10 years ago

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. 

  • pqian's avatar
    pqian
    Microsoft Employee
    10 years ago

    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. 

    • rjhale's avatar
      rjhale
      Helper V
      10 years ago

      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:

       

      • pqian's avatar
        pqian
        Microsoft Employee
        10 years ago

        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?