Power BI: DataSource.Error: The table has no visible columns and cannot be queried

Subject: Issue Importing MongoDB Data into Power BI

Hello MongoDB community,

I’m encountering an issue importing data from MongoDB into Power BI and would appreciate the community’s assistance in resolving it.

Currently, I am using Power BI to create some metrics, and the database I am using comes from MongoDB. In MongoDB, I have three collections: twitterpublicmetrics, twitterpercentages, and twitternewmetrics. All collections have recorded data.

The problem arises when I try to import data from the twitterpercentages and twitternewmetrics collections using the following links in the Power Query editor:

= database_Database{[Name="twitterpercentages",Kind="Table"]}[Data]
= database_Database{[Name="twitternewmetrics",Kind="Table"]}[Data]

Upon doing this, I receive the following errors:

  • Error 1:

    DataSource.Error: The table has no visible columns and cannot be queried.
    Details: twitterpercentages
    
  • Error 2:

    DataSource.Error: The table has no visible columns and cannot be queried.
    Details: twitternewmetrics
    

Here are some examples of data in the collections to provide more context:

Example data in twitterpublicmetrics:

{"_id":{"$oid":"6555da10b2ae530e91a1fb74"},"posicao":{"$numberInt":"0"},"data":"16/11/2023","horario":"06:00","seguidores":{"$numberInt":"239"},"seguindo":{"$numberInt":"238"},"contagemTweets":{"$numberInt":"160"},"listasParticipadas":{"$numberInt":"4"},"likesRecebidos":{"$numberInt":"173"},"__v":{"$numberInt":"0"}}

Example data in twitterpercentages:

{"_id":{"$oid":"65572b90b2ae530e91a1fb7b"},"posicao":{"$numberInt":"1"},"data":"17/11/2023","horario":"06:00","aumentoSeguidores":{"$numberDouble":"0.8368200836820083"},"aumentoSeguindo":{"$numberInt":"0"},"aumentoTweets":{"$numberInt":"0"},"aumentoListas":{"$numberInt":"0"},"aumentoLikes":{"$numberInt":"0"},"__v":{"$numberInt":"0"}}

Example data in twitternewmetrics:

{"_id":{"$oid":"65572b90b2ae530e91a1fb7d"},"posicao":{"$numberInt":"1"},"data":"17/11/2023","horario":"06:00","novosSeguidores":{"$numberInt":"2"},"novosSeguindo":{"$numberInt":"0"},"novosTweets":{"$numberInt":"0"},"novosListas":{"$numberInt":"0"},"novosLikes":{"$numberInt":"0"},"__v":{"$numberInt":"0"}}

Error:

Does anyone have any ideas on how to resolve these errors? I appreciate any assistance!

Best regards,
Vinícius Costa

Hello @MTP_Brasil and welcome to the community! This is likely an issue with the SQL Schema and you just need to regenerate it. Check out these instructions or go to our online docs for even more information: https://www.mongodb.com/docs/atlas/data-federation/query/sql/sqlgenerateschema/

Best,
Alexi

I believe creating a video on this topic would be immensely beneficial for analytics professionals, providing valuable insights and enhancing their understanding.

@rohan_dhunde - very good idea. We have some new UI coming to allow users the ability to control the SQL Schema within Atlas (you won’t need to do this within Mongosh). This will be released in April 2024. I will create a video on managing SQL Schemas to complement a blog post about the new UI (user interface) in Atlas.

1 Like

I attempted to establish a connection between MongoDB Atlas and Power BI, but encountered an error: ‘DataSource.Error: The table has no visible columns and cannot be queried.’ Could you please guide me on how to resolve this issue? I’d appreciate any recommendations for relevant documentation or instructional videos. and i mensioned the image of the error thats i found.

Screenshot 2024-03-28 113328

@rohan_dhunde - this error occurs when there isn’t a sql schema created for your underlying collection/table. Please follow these steps to generate the SQL Schema for this collection or for the whole federated database instance:

This functionality to regenerate the SQL Schema will soon be available within the Atlas UI (saving you the trip to mongosh). But for now, if you log into mongosh and run the sqlGenerateSchema command for this collection that should do the trick. It typically gets created automatically, but perhaps this did not happen or the shape of the collection changed dramatically.