Getting this error: datasource.error: the table has no visible columns and cannot be queried.

[alozano93]

Hello community,

I am currently working on extracting data from our MongoDB database and have encountered an issue that I would like to discuss.

The problem I am facing is related to one specific collection within the database. When attempting to query this collection, I receive the following error message: "datasource.error: the table has no visible columns and cannot be queried."

To provide some context, here is a sample of the data I obtained when exporting this collection from MongoDB:

_id	userId	companyId	createdAt	updatedAt	__v
64d2bc3ce3860edadd7a13e1	64c9f48f56c74a3fd5aa58b9	64a44e840adca93e5f22a3c6	2023-08-08T22:05:48.348Z	2023-08-08T22:05:48.348Z	0
64d2bc3ce3860edadd7a13e2	64a79b946e3643fc11391c9f	64a44e840adca93e5f22a3c6	2023-08-08T22:05:48.348Z	2023-08-08T22:05:48.348Z	0
64d2bc3ce3860edadd7a13e4	64a79b946e3643fc11391c9f	64a44e840adca93e5f22a3c6	2023-08-09T22:05:48.348Z	2023-08-09T22:05:48.348Z	0
64d2bc3ce3860edadd7a13e3	64a79b946e3643fc11391c9f	64a44e840adca93e5f22a3c6	2023-10-08T22:05:48.348Z	2023-10-08T22:05:48.348Z	0

What is weird is that the error message suggests that there are no visible columns in the collection, but as you can see, the data contains several columns with meaningful values.

I have attempted various solutions to resolve this issue, including renaming the collection, resetting the database, and even creating a new collection. However, I consistently encounter the same error. Even when creating a new dashboard, the problem persists.

The data types used in this collection include ObjectId, String (x2), Date (x2), and Int32. Could the use of these data types be a contributing factor to the issue?

I have diligently searched for information regarding this specific error related to MongoDB but have not found any relevant insights or solutions.

I would greatly appreciate any guidance or assistance you can provide in resolving this matter. Your expertise and support in addressing this challenge would be invaluable.

Thank you for your time and assistance.

[jan-victor]

Anybody has a fix on that?
run into the same issue.
we added some collections to the database, but they are basically replicas of previous collections.
so no new fields added to the documents. it still throws the same error on the new collections.
also not nested or complicated data. one collection has exactly one field so this is strange.
also checked that data is healthy, so no errors (missing values, different value types on the same property etc.)

[jan-victor]

Solution:

Seems to be that when adding new collections you need to update the SQL Schemas in the Data Federation for Atlas SQL in MongoDB.

Its under
Services > Data Federation > Scroll Down to your cluster > ManageSQL Schemas > Find the Collection that doesn't work > will be marked as "empty" > click edit (pencil icon) > generate schema

this did it for me

[Anonymous]

Hi @alozano93 ,

This error might be caused by one of the following reasons:

• The data source connection is not configured properly. You might need to check your credentials, server name, database name, and port number in the Data source settings.
• The collection schema is not compatible with Power BI. You might need to modify the schema or use a different connector to access your data.
• The collection contains nested or complex data types that Power BI cannot handle.

To troubleshoot this issue, you can try the following steps:

• Refresh the data source and make sure it is up to date.
• Use the MongoDB Connector for BI instead of the ODBC connector. This connector can handle more complex data types and schemas.
• Use the Power Query Editor to preview and shape your data. You can apply filters, transformations, and aggregations to your data before loading it into Power BI.

Solved: The table has no visible columns and cannot be que... - Microsoft Fabric Community

Incorrect Warning Message, “This data source contains no visible - Microsoft Community

apache spark - Querying Delta.io tables with Power BI returns the "The table has no visible columns"...

How to Get Your Question Answered Quickly 

If it does not help, please provide more details.

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[alozano93]

Hi @Anonymous, Thanks for your support.

• The data source connection is not the issue, I’m working with 20 other collections from the database and using the same connection.
• It doesn’t have nested/complex data types, other collections have them and it doesn’t seem like an issue.
• The data is up to date.
• I’m using MongoDB Atlas SQL.

• Cannot get to Power Query Editor with that one:

• If I use any other collection, no problem:

• Solved: The table has no visible columns and cannot be que... - Microsoft Fabric Community : In this case, the user cannot pull any table, I’m having an issue with just one in particular and I have the latest Gateway installed.
• Incorrect Warning Message, “This data source contains no visible - Microsoft Community

apache spark - Querying Delta.io tables with Power BI returns the "The table has no visible columns"... : In this case, spark issue.

Any other insight will be appreciated.