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
Highlighted
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

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! :) 

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? 

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. 

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

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies