Forum Discussion
Import Excel workbook datamodel from OneDrive
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.
Let me clarify what i am looking for
1. Connect Power BI desktop to an XLSM file which is stored on OneDrive
2. import the entire data model
3. Create new visuals in the desktop
4. publish report to power bi service
5. have automatic refresh enabled in power bi service with the XLSM file which is stored on OneDrive
Thank you for clarifying.
Step #5 is not supported in your scenario. The File-->Import step (step 2) is a one time effort that was designed to migrate from Excel files to Power BI report files.
Your only options at this stage are the following ones:
Option 1 - Keep the Excel XLSM files on OneDrive as a data source. Don't use the Data Model, but the grid. Then start a Power BI Desktop report (PBIX) that uses Get Data from Web to load the data from these Excel files. For a refresh, you can automate the refresh of the Excel files using VBA/Object Model. Your Power BI service can be configured to schedule a daily refresh from OneDrive for Business.
Option 2 - Use Power Update to perform the client side refresh on your PBIX file.