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

[Anonymous]

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!

[v-qiuyu-msft · Accepted solution]

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:

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.

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.

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