ODBC Preview Error

[coatsy35]

Hi,

I have an ODBC connection to data for our business systems. When viewing the tabels in Power Query or Power BI quite a few of the tables do not show, they instead show the following error so that I cannot transform anyting in the table etc....

I can load the table fine to excel and all data shows correctly if I don't wish to transform the data. (The one erroring in the picture currently only has 4 rows and 182 columns). I hear mention it might be to do with it trying to create relasionships automatically but I see no options anywhere to turn that off. The actual database has about 400 tables in it of varying sizes.

Can anyone shed any light on this?

[rajendraongole1]

Hi @coatsy35 -I think in your power query editor certain tables from your ODBC connection show an error like "The type of the current preview value is too complex to display"—can stem from various factors. Here are some common troubleshooting steps you can take:

1. Check Data Types: Sometimes, complex data types (like nested structures or very wide tables) can cause preview issues. You might try simplifying the data type or using SQL queries to limit the columns returned.

2. Disable Auto Relationships: Power BI tries to create automatic relationships based on matching column names, which may lead to issues with some tables. You can disable this feature:

   • Go to File > Options and settings > Options.
   • Under Global > Data Load, uncheck "Auto date/time for new files" if applicable.
   • For Relationships, make sure that "Auto-detect new relationships after data is loaded" is turned off.

3. Use SQL Queries: Instead of directly pulling the entire table, consider writing a custom SQL query to select only the columns you need, or to aggregate or filter data in a way that simplifies the result set.

4. Inspect Column Names: If any of your column names are particularly long or complex, try renaming them in the database, as this might help Power BI handle the schema better.

Hope this information and tips helps

[coatsy35]

Ok, so if I enter CreateNavigationProperties=false into the advance editor it works fine. However I can't find a way to turn this off either at the driver or on Power BI / excel options so it doesn't do it each time, which is tedious to say the least.

At the moment I have to load each table and then edit the query in the advanced editor, below is a picture of my Power Bi settings, there is nothing relating to navigation properties in dataload on either Global or Current workbook, disabling the relationship setting in current workbook does nothing. I assume it can't be done at the driver either?

This is what my advance tab now looks like, whihc works. But I don't want to have to do this for every table