Forum Discussion

coatsy35's avatar
coatsy35
Helper II
1 year ago

ODBC Preview Error

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 ...
Need Help
rajendraongole1's avatar
rajendraongole1
Super User
1 year ago

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's avatar
coatsy35
Helper II
1 year ago

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