Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mahimabedi
Responsive Resident
Responsive Resident

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 9

Hi @mahimabedi,

 

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.

Screenshot_15.png

 

 

And provide your credentials to OneDrive for Business:

 

Screenshot_14.png

 

 

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

 

Screenshot_19.png

 

In the FIle dialog, browser to the OneDrive for Business location and change the file type from Excel Files (*.xlsx) to All Files (*.*)Screenshot_18.png

 

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 

 

 

 


@mahimabedi 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 @mahimabedi,

 

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.

 

q3.PNG

 

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.

 

q2.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi,

 

 

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

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.