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
Anonymous
Not applicable

Refreshing OneDrive for Business Excel Files with data model and Linked table

Hi everyone!

 

When I have a table of data in an Excel worksheet hosted on OneDrive for business, the data refreshes perfectly (manual or scheduled). 

 

However, added that table from the worksheet to the data model by going to Power Pivot > Add to data model.  Then, I resaved it and changed some information in both the table and data model, which is linked to the table in the worksheet. When I try to refresh in Power BI service, I will get "unknown" errors, or all of the data will appear blank. From the articles and posts I've read on here, it seems that a linked table should be a pretty seamless refresh experience. Ideally, it would be nice to create measures and KPI in the data model and have those accesible when building reports in Power BI and have them refreshable as well. Any tips would be great!

 

Thanks!

1 ACCEPTED SOLUTION
v-qiuyu-msft
Community Support
Community Support

Hi @Anonymous,

 

Based on my test, I uploaded a Excel file to OneDrive for Business which contains a table data without any external connection. In Power BI Service, connect to Excel via "Connect, Manage, and View Excel in Power BI". Then I edit the Excel to add data model and pivot table. Back to Power BI Service, refresh the workbook and PivotTable displays. See:

 

q2.PNG

 

If I connect to Excel via "Import Excel data into Power BI", and create a report from this dataset. Then edit Excel to create data model and PivotTable, and back to Power BI Service to refresh the dataset via "Refresh Now", the unknown error will throw out and data model structure will not updated in the dataset.

 

q3.PNG

 

In my opinion, Excel table and data model are different things, Power BI dataset can't update table to data model structure. So you need to re-import data model to Power BI again.

 

q4.PNGq5.PNG

 

In addition, if your want to change workbook from the table to data model instead of change table data, please use "Connect, Manage, and View Excel in Power BI" method, as in this way refreshed data goes into the workbook's data model on OneDrive, or SharePoint Online, rather than a dataset in Power BI.

 

Reference:
Refresh a dataset created from an Excel workbook on OneDrive, or SharePoint Online

 

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.

View solution in original post

1 REPLY 1
v-qiuyu-msft
Community Support
Community Support

Hi @Anonymous,

 

Based on my test, I uploaded a Excel file to OneDrive for Business which contains a table data without any external connection. In Power BI Service, connect to Excel via "Connect, Manage, and View Excel in Power BI". Then I edit the Excel to add data model and pivot table. Back to Power BI Service, refresh the workbook and PivotTable displays. See:

 

q2.PNG

 

If I connect to Excel via "Import Excel data into Power BI", and create a report from this dataset. Then edit Excel to create data model and PivotTable, and back to Power BI Service to refresh the dataset via "Refresh Now", the unknown error will throw out and data model structure will not updated in the dataset.

 

q3.PNG

 

In my opinion, Excel table and data model are different things, Power BI dataset can't update table to data model structure. So you need to re-import data model to Power BI again.

 

q4.PNGq5.PNG

 

In addition, if your want to change workbook from the table to data model instead of change table data, please use "Connect, Manage, and View Excel in Power BI" method, as in this way refreshed data goes into the workbook's data model on OneDrive, or SharePoint Online, rather than a dataset in Power BI.

 

Reference:
Refresh a dataset created from an Excel workbook on OneDrive, or SharePoint Online

 

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.

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.

Top Solution Authors
Top Kudoed Authors