I have seen older threads related to this however i have not found anything that is definitive as to the cause. I get this on one table from a SQL Server database. Tried Direct Query & Import. I have other tables far more complex than this one that import with no issues. If i open the native query window and enter "select * from dbo.coitem_mst" i get the data with no issue. I am using version 2.79. I am hoping someone has some addtional clarity on what causes this error.

Update - After discussion with Microsoft, below is the detail response along with solution that is working for me. I added the below property in M Query - [CreateNavigationProperties=false] and it is working. "It has come to our attention that the Mashup Engine enforces a soft limit of 10MB for type value serialization. This limitation is often attributed to a significant number of relationship columns, particularly evident in CRM data sources.As a supplementary measure to the previously shared workaround, there exists an alternative approach where we can instruct the engine to avoid using Relationships by using the following code snippet:Source = CommonDataService.Database("msdefault.crm.dynamics.com", [CreateNavigationProperties=false])"

I had this string as my source""= Odbc.DataSource("dsn=MyODBCDataSoure", [HierarchicalNavigation=true])"It started working when I changed it to the following :"= Odbc.DataSource("dsn=MyODBCDataSoure", [HierarchicalNavigation=true, CreateNavigationProperties=false])"Just for other people to see that multiple options can be added, just separate it with a comma.

Encountering as well using NetSuite ODBC

Outcome from the MS support team was that there are too many relatioships. Seems a bit odd as our tables such as users are just standard Dynamics tables and nothing has changed for a long time but anyway. In the end they suggested creating the connection and edditing the M code to limit the number ofrelationships. Example M code: let Source = Sql.Databases("LAPTOP-IDOG6CV8"), AdventureWorks = Source{[Name="AdventureWorks"]}[Data], dbo_DatabaseLog = AdventureWorks{[Schema="dbo",Item="DatabaseLog"]}[Data],#"Removed Columns" = Table.RemoveColumns(dbo_DatabaseLog,{"PostTime", "XmlEvent"})in #"Removed Columns"

Hi BarrilleauxAP , Based on my test using Power bi desktop Version: 2.79.5768.1082 64-bit (March 2020). I cannot reproduce your issue here. Could you please update your Power bi desktop to check again or you can disable the option to have a test.

Preview.Error: The type of the current preview value is too complex to display

25 Replies

Syndicate_Admin
Administrator
3 years ago
Update - After discussion with Microsoft, below is the detail response along with solution that is working for me. I added the below property in M Query - [CreateNavigationProperties=false] and it is working.

"It has come to our attention that the Mashup Engine enforces a soft limit of 10MB for type value serialization. This limitation is often attributed to a significant number of relationship columns, particularly evident in CRM data sources.
As a supplementary measure to the previously shared workaround, there exists an alternative approach where we can instruct the engine to avoid using Relationships by using the following code snippet:
Source = CommonDataService.Database("msdefault.crm.dynamics.com", [CreateNavigationProperties=false])"
- Johannf78
  Advocate I
  3 years ago
  I had this string as my source"
  "= Odbc.DataSource("dsn=MyODBCDataSoure", [HierarchicalNavigation=true])"
  It started working when I changed it to the following :
  "= Odbc.DataSource("dsn=MyODBCDataSoure", [HierarchicalNavigation=true, CreateNavigationProperties=false])"
  Just for other people to see that multiple options can be added, just separate it with a comma.
- NamNguyenVN
  New Member
  3 years ago
  Thank you for sharing so well
- Shaqter
  New Member
  2 years ago
  That worked for me. Thanks for sharing it!
monpin
Regular Visitor
4 years ago
Encountering as well using NetSuite ODBC
v-frfei-msft
Community Support
6 years ago
Hi BarrilleauxAP ,

Based on my test using Power bi desktop Version: 2.79.5768.1082 64-bit (March 2020). I cannot reproduce your issue here. Could you please update your Power bi desktop to check again or you can disable the option to have a test.
- BarrilleauxAP
  Regular Visitor
  6 years ago
  I confirmed that i am running that version.
  
  I also disabled the option you recommended and tried again but obtained the same result.
- pcalhoun
  New Member
  6 years ago
  I am having the same error when I am pulling data from Netsuite.
  
  I could use help solving this problem.
  
  Thanks
  
  Phil
- kreiss
  Frequent Visitor
  6 years ago
  Add me to the list. Same issue. Even on a really small (12 row, 6 column) table. Help!
Syndicate_Admin
Administrator
4 years ago
Add me to the list of users who have this issue when trying to grab ODBC data in Excel from Netsuite. Any help to solve this issue would be greatly appreciated. I see lots of these errors when searching for solutions, but no solutions.
Syndicate_Admin
Administrator
4 years ago
Hi,

I am also getting this issue , is there any specific cause for this.
I have tried the setting changes mentioned but no success.

Kindly let me know if there is any possible fix for this issue.

Thanks
Syndicate_Admin
Administrator
4 years ago
How did everyone go resolving this? We have just purchased Netsuite ODBC and having the same error message loading to POwer Query. Thnks
- Syndicate_Admin
  Administrator
  4 years ago
  @Syndicate_Admin Can you pleeease help?
Syndicate_Admin
Administrator
4 years ago
Hi,

I explored more on this and I believe this can be data issue in Systemuser Table.
Also , the following error is displayed to me when I tried to "Close and Apply"

"Column territoryidname in systemusertable contains blank values and this is not allowed for columns on the one side of many-to-one relationship or for columns that are used as primary key of table".

Kindly let me know what can cause this blank value and why I am getting this on one environment and not others.

Thanks
Syndicate_Admin
Administrator
4 years ago
For us the solution is to create views. We have just starting using power query and power bi and we have no views set up. The customer table I am getting the error with is pretty raw data and a lot of columns, not many rows. Also could be that it is trying to load the table relationships. Though I had this setting off and it still wouldn't work.

So even a simple table may have too many columns or could have too many relationships. If you can create a SQL on the table and it works that way but doesn't work on power query import then create views to simplify to import.
Syndicate_Admin
Administrator
3 years ago
Same issue I am also facing under Power BI while connecting to Dataverse Table: Site
= Source{[Schema="dbo",Item="site"]}[Data]

I has very few table columns and table rows but still failing to load. Also I have same issue for contact table but other complex column type and huge volume column value tables are loaded successfully. How to fix dataverse table loading?
- Syndicate_Admin
  Administrator
  3 years ago
  Hi @sangarganesh
  
  Only fix I came up with is to keep only the required columns and remove rest of the columns.
  This problem might be because of complex relationships between table columns
richrich123
Helper III
3 years ago
I have just run into the same issue. I have multiple PBI's that all use the dynamics systemuser table and never had an issue. I have just upgraded PBI destop to latest version this morning and gone to create a new report. Added 3 tables, 2 came in fine, users is refusing to let me preview/transform/edit.

I also dont seem to be able to do ctrl or Shft and select multiple columns to delete what I don't need.