Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
alozano93
Frequent Visitor

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

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:

 

_iduserIdcompanyIdcreatedAtupdatedAt__v
64d2bc3ce3860edadd7a13e164c9f48f56c74a3fd5aa58b964a44e840adca93e5f22a3c62023-08-08T22:05:48.348Z2023-08-08T22:05:48.348Z0
64d2bc3ce3860edadd7a13e264a79b946e3643fc11391c9f64a44e840adca93e5f22a3c62023-08-08T22:05:48.348Z2023-08-08T22:05:48.348Z0
64d2bc3ce3860edadd7a13e464a79b946e3643fc11391c9f64a44e840adca93e5f22a3c62023-08-09T22:05:48.348Z2023-08-09T22:05:48.348Z0
64d2bc3ce3860edadd7a13e364a79b946e3643fc11391c9f64a44e840adca93e5f22a3c62023-10-08T22:05:48.348Z2023-10-08T22:05:48.348Z0

 

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.

4 REPLIES 4
jan-victor
New Member

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.)

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

v-rongtiep-msft
Community Support
Community Support

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.

 

 

 

 

Hi @v-rongtiep-msft, 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:

alozano93_0-1694783399147.png

 

  • If I use any other collection, no problem:

alozano93_1-1694783399125.png

 

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.