Forum Discussion
Import Excel workbook datamodel from OneDrive
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.
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."
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
It works with regular xlsx files. But i am trying to import a xlsm file which is on OneDrive, using the Power BI desktop.
Importing the contents by file>import imports the entire xlsm data model with relationships if the file is saved locally. Trying to do the same with a xlsm file saved on OneDrive.
Could you please explain if you are intersted in one time import from Excel XLSM file to a PBIX report, or do you look for a refresh scenario from XLSM on OneDrive for Business to the PowerBI.com service?
If you are looking on the first scenmario, you can import XLSM files like XLSX files.
Start in Power BI Desktop --> File --> Import --> Excel Workbook Content
In the FIle dialog, browser to the OneDrive for Business location and change the file type from Excel Files (*.xlsx) to All Files (*.*)
Then you will see you XLSM file and will be able to import its Data Model like you did in the XLSX file.