Home

Excel File with Data Model in SharePoint Online

%3CLINGO-SUB%20id%3D%22lingo-sub-38037%22%20slang%3D%22en-US%22%3EExcel%20File%20with%20Data%20Model%20in%20SharePoint%20Online%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-38037%22%20slang%3D%22en-US%22%3E%3CP%3EI%E2%80%99ve%20been%20working%20to%20move%20a%20lot%20of%20my%20Excel%20reports%26nbsp%3Bto%20SharePoint%20from%20our%20file%20shares%20and%20have%20our%20users%20interact%20with%20them%20either%20directly%20through%20Excel%20online.%20%26nbsp%3BA%20lot%20of%20my%20reports%20contain%20data%20that%20reside%20in%20the%20workbook%E2%80%99s%20data%20model%20(PowerPivot).%26nbsp%3B%20It%20is%20my%20understanding%20the%20SharePoint%20online%20does%20support%20workbooks%20with%20a%20data%20model%2C%20and%20in%20fact%2C%20everything%20seems%20to%20work%20as%20expected%20in%20our%20test%26nbsp%3Btenant.%26nbsp%3B%20However%3B%20the%20exact%20same%20file%20does%20not%20work%20in%20production%2C%20and%20I%20get%20an%20error%20stating%20%E2%80%9CThis%20workbook%20contains%20external%20data%20connections%20or%20BI%20features%20that%20are%20not%20supported%E2%80%9D%20when%20I%20attempt%20to%20filter%20the%20pivot%20table%20in%20anyway.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20file%20that%20I'm%20testing%20with%20is%20a%26nbsp%3Bbasic%20Excel%20file%2C%20that%20contains%20no%20external%20data%20connections%2C%20and%20the%20pivot%20table%E2%80%99s%20data%20source%20is%20a%20table%20in%20the%20data%20model.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20are%20some%20of%20the%20things%20I%E2%80%99ve%20tried%20and%20verified%20to%20this%20point%20(our%20test%20tenant%20is%20first-release%20enabled)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3EMade%20sure%20my%20test%20tenant%20collection%20and%20production%20collection%20matched%20on%20enabled%20site%20collection%20features%3C%2FLI%3E%3CLI%3ESame%20thing%20as%20above%20just%20for%20the%20individual%20site%20features%3C%2FLI%3E%3CLI%3EIn%20the%20test%20tenant%2C%20I%20confirmed%20that%20it%20worked%20with%20multiple%20accounts%2C%20some%20with%20the%20free%20Power%20BI%20license%20applied%20and%20others%20without%3C%2FLI%3E%3CLI%3EIn%20the%20production%20tenant%2C%20I%20tried%20with%20both%20an%20account%20that%20was%20synced%20through%20AAC%20and%20one%20that%20was%20created%20directly%20in%20the%20Office%20365%20admin%20interface.%26nbsp%3B%20Same%20error%20message%20for%20both%20accounts.%3C%2FLI%3E%3CLI%3EI%E2%80%99ve%20tried%20opening%20the%20file%20in%20Excel%20online%20in%20multiple%20sites%20in%20both%20test%20and%20production.%26nbsp%3B%20The%20results%20are%20the%20same%20throughout%2C%20I%20can%20filter%20the%20pivot%20table%20in%20test%20but%20I%20can%E2%80%99t%20in%20production.%3C%2FLI%3E%3C%2FOL%3E%3CP%3EIs%20there%20a%20SharePoint%20setting%20or%20a%20feature%20that%20needs%20to%20be%20enabled%20to%20allow%20advanced%20analytic%20featues%20such%20as%20a%20PowerPivot%20data%20models%20to%20function%20in%20Excel%20Online%3F%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-38037%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-40883%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20File%20with%20Data%20Model%20in%20SharePoint%20Online%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-40883%22%20slang%3D%22en-US%22%3E%3CP%3ERiley%2C%20thank%20you%20for%20the%20link%2C%20i%20missed%20that%20Preview%20Feature.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-40872%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20File%20with%20Data%20Model%20in%20SharePoint%20Online%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-40872%22%20slang%3D%22en-US%22%3E%3CP%3EI%20don't%20believe%20that%20we%20are%20in%20the%20government%20cloud.%20%26nbsp%3BWe%20did%20find%20that%20this%20is%20indeed%20related%20to%20enabling%20Preview%20Features%20in%20the%20SharePoint%20Admin%20portal.%20%26nbsp%3BThis%20is%20a%20known%20issue%2C%20and%20I%20don't%20know%20when%20it%20will%20be%20available%20without%20enabling%20Preview%20Features%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fanswers.microsoft.com%2Fen-us%2Fmsoffice%2Fforum%2Fmsoffice_sharepoint-mso_winother%2Fsharepoint-online-excel-bi-improvments%2Fb43eb0e8-9fcd-4c6d-829f-e709788c0763%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fanswers.microsoft.com%2Fen-us%2Fmsoffice%2Fforum%2Fmsoffice_sharepoint-mso_winother%2Fsharepoint-online-excel-bi-improvments%2Fb43eb0e8-9fcd-4c6d-829f-e709788c0763%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20following%20up%20though.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-40051%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20File%20with%20Data%20Model%20in%20SharePoint%20Online%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-40051%22%20slang%3D%22en-US%22%3E%3CP%3ERiley%2C%20I've%20heard%20the%20same%20thing%20about%20govt%20tenants%20that%20Cyrus%20mentioned.%20However%2C%20are%20both%20your%20test%20and%20production%20tenants%20in%20the%20govt%20cloud%3F%20Were%20they%20created%20at%20the%20same%20time%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-39261%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20File%20with%20Data%20Model%20in%20SharePoint%20Online%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-39261%22%20slang%3D%22en-US%22%3E%3CP%3EI%20see%20you%20are%20with%20the%26nbsp%3B%3CSPAN%3EUniversity%20of%20Wisconsin%3F%20%26nbsp%3BIs%20your%20Office%20365%20tenant%20in%20the%20govt%20Cloud%3F%20%26nbsp%3BI%20found%20out%20the%20hard%20way%20that%20Data%20models%20in%20Excel%20ARE%20NOT%20supported%20in%20the%20govt%20space%20as%20this%20feature%20was%20never%20ported%20to%20that%20build.%20%26nbsp%3Bdouble%20check%20with%20MS%20Support%20to%20verify%20but%20this%20was%20my%20expereince%20back%20when%20I%20worked%20for%20the%20govt.%20%26nbsp%3BSo%20much%20better%20working%20the%20private%20sector%20where%20I%20can%20actually%20use%20new%20technology!%20%3A)%3C%2Fimg%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Riley Hale
Contributor

I’ve been working to move a lot of my Excel reports to SharePoint from our file shares and have our users interact with them either directly through Excel online.  A lot of my reports contain data that reside in the workbook’s data model (PowerPivot).  It is my understanding the SharePoint online does support workbooks with a data model, and in fact, everything seems to work as expected in our test tenant.  However; the exact same file does not work in production, and I get an error stating “This workbook contains external data connections or BI features that are not supported” when I attempt to filter the pivot table in anyway.

 

The file that I'm testing with is a basic Excel file, that contains no external data connections, and the pivot table’s data source is a table in the data model. 

 

Here are some of the things I’ve tried and verified to this point (our test tenant is first-release enabled):

 

  1. Made sure my test tenant collection and production collection matched on enabled site collection features
  2. Same thing as above just for the individual site features
  3. In the test tenant, I confirmed that it worked with multiple accounts, some with the free Power BI license applied and others without
  4. In the production tenant, I tried with both an account that was synced through AAC and one that was created directly in the Office 365 admin interface.  Same error message for both accounts.
  5. I’ve tried opening the file in Excel online in multiple sites in both test and production.  The results are the same throughout, I can filter the pivot table in test but I can’t in production.

Is there a SharePoint setting or a feature that needs to be enabled to allow advanced analytic featues such as a PowerPivot data models to function in Excel Online?  

 

Thanks. 

4 Replies
Highlighted

I see you are with the University of Wisconsin?  Is your Office 365 tenant in the govt Cloud?  I found out the hard way that Data models in Excel ARE NOT supported in the govt space as this feature was never ported to that build.  double check with MS Support to verify but this was my expereince back when I worked for the govt.  So much better working the private sector where I can actually use new technology! :) 

Highlighted

Riley, I've heard the same thing about govt tenants that Cyrus mentioned. However, are both your test and production tenants in the govt cloud? Were they created at the same time? 

Highlighted

I don't believe that we are in the government cloud.  We did find that this is indeed related to enabling Preview Features in the SharePoint Admin portal.  This is a known issue, and I don't know when it will be available without enabling Preview Features:

 

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_sharepoint-mso_winother/sharepoint-onlin...

 

Thanks for following up though. 

Highlighted

Riley, thank you for the link, i missed that Preview Feature.

Related Conversations
Excel Worksheets and templates
Christine_R in Excel on
2 Replies
Web links no longer works after data refresh
DataQue in Excel on
0 Replies
Percentage of attendance
linrojy in Excel on
6 Replies
Grouped data visibility in formula builder
Bram_Elfrink in Excel on
1 Replies
Master excel that affects other files
rubenpd1 in Excel on
2 Replies
Continuous page break in Excel on Mac
Henri1010 in Excel on
2 Replies