Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Import Excel workbook datamodel from OneDrive

Using Power bi desktop: How do i import an excel data model from a file stored on onedrive. Using the webconnector doesnt seem to bring in the data model or the Powerview tables? And using the file>import, does not allow me to connect to excel stored on onedrive.

9 Replies

  • DataChant's avatar
    DataChant
    Most Valuable Professional
    9 years ago

    Hi Anonymous,

     

    Are you looking for a one-time import of the Data Model to a .PBIX file?

    The File --> Import should work. You can copy the Data Model from an Excel workbook to Power BI Desktop. Just ensure that your file system is mapped to One Drive for Business, here is how to setup a folder on your computer.

     

    If you are looking for an auto refresh - You cannot connect to the Excel Data Model from powerbi.com service. The service can only connect to Excel data on the grid and CSV files.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      the documentaion mentions that we can use the "web" connector to connect to a data model on ONedrive and that will pull in all the data model with relationships and power view etc. But this doesnt seem to work.

       

      https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-import-excel-workbooks/

       

      "Note: To load or import Excel files from shared OneDrive for Business folders or from Office 365 group folders, use the URL of the Excel file, and input it into the Web data source in Power BI Desktop. There are a few steps you need to follow to properly format the OneDrive for Business URL, so check out Use OneDrive for Business links in Power BI Desktop for more information and the correct series of steps."

      • DataChant's avatar
        DataChant
        Most Valuable Professional
        9 years ago

        It should work. You can get a daily auto-refresh, or hourly if you upgrade to Power BI Pro.

        Can you share the query (in Advanced Editor from Query Editor)?

         

        When I tested it, I used this line to import XLSX file from OneDrive for Bussiness:

         

        Source = Excel.Workbook(Web.Contents("https://datachant-my.sharepoint.com/personal/gilra_datachant_com/Documents/Auto Refresh Test.xlsx"), null, true),

        I used this line to import CSV file from OneDrive for Business:

         

         

        Source = Table.FromColumns({Lines.FromBinary(Web.Contents("https://datachant-my.sharepoint.com/personal/gilra_datachant_com/Documents/Auto Refresh Test.csv"), null, null, 1252)}),

         

        You should also configure the Auto Refresh settings under the specific dataset in PowerBI.com.

         

         

        And provide your credentials to OneDrive for Business:

         

         

         

        Hope it helps,

        Gil