Forum Discussion
Import Excel workbook datamodel from 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.
Anonymous wrote:
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
Hi Anonymous,
In Power BI desktop, we can use web data source to get data from .xlsm workbook, but we can't import PowerPivot table, PowerView table and so on. You can use the Import Workbooks feature suggested by DataChant, you can navigate to the folder in OneDrive for Buiness.
In your scenario, I would suggest you connect to this .xlsm workbook in Power BI Service use "Connect, Manage, and View Excel in Power BI". For more information, please see: Refresh a dataset created from an Excel workbook on OneDrive, or SharePoint Online.
Best Regards,
Qiuyun YuHi,
I am facing the exact problem. The issue has been escalated and we are waiting for some news from the MSFT Team. The top 2 issues in the following link are related with the problem: http://community.powerbi.com/t5/Issues/idb-p/Issues (http://community.powerbi.com/t5/Issues/Invalid-Credentials-on-Scheduled-Refresh/idi-p/92103 and http://community.powerbi.com/t5/Issues/Invalid-credentials-on-Sharepoint-folder/idi-p/93059)
Regards
