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
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
historical stock data
onassis78 in Excel on
0 Replies
Excel cell formatting
snojoe10 in Excel on
1 Replies
Corrector automático
jorgf2047 in Excel on
0 Replies
Problem when opening Excel
LarryC1940 in Excel on
0 Replies