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

[BarrilleauxAP]

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.

[Syndicate_Admin]

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])"

[NamNguyenVN]

Thank you for sharing so well

[Johannf78]

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.

[Syndicate_Admin]

Thanks for the update. Really appreciate.

Well in my case, I am trying to connect only 1 table which is not even users, and no other tables exist to make a possible relationship. but yes, I tried selecting column in m code and the preview did work. but issue is, we cannot hardcode all the column we need in m code, quite challenging. in fact, I use power bi to see all columns and data in a table as I don't have access to SQL or any other where I can write queries and know until i ask the developers to provide me list of column names. Well thanks a lot for the update, we can consider as a could be solution.

[richrich123]

I did the same as a test, just took the users table as a single entity. Also tried with just case resoultion entity and the same. This was why I raised a ticket with MS becuase I couldn't see how/why it can be a relationship issue with only a single table but they have said that is the case and it relates to all the relations that exist from the table, even if you arent getting/using the others and the only way around it is to edit the M code to limit your columns initially and then add more in which I have ended up doing using the M query tool in xrmtoolbox. Not idea as it did take seconds and now it takes much more messing about just to get a simple table

[ROC]

Same with ODBC Driver... It's new ? any update please ? 

[richrich123]

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 of

relationships. 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"

[Syndicate_Admin]

I tested it today in Dev and Pre prod environments and preview is available and working, but in Live Production, it's not. for some tables, it's working in prod as well. system user has less data but gives error and takes more than 80% of memory while evaluating and never coming back. some tables in production working fine. 

[Syndicate_Admin]

Getting the same error since days.  

Version: 2.115.663.0 64-bit (March 2023)

Still haven't figured this issue.

[richrich123]

I have a case open with MS on this. Just done an hour with an engineer, unable to get a resoltion as yet after the session, he has gone to further investigate. Will uopdate when I get a resolution.

[richrich123]

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. 

[Syndicate_Admin]

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]

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

[Syndicate_Admin]

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.

[monpin]

Encountering as well using NetSuite ODBC

[Syndicate_Admin]

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]

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]

@Syndicate_Admin  Can you pleeease help?

[Syndicate_Admin]

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]

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.