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

[MTP_Brasil]

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

[Alexi_Antonino]

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://mongodb.prakticum-team.ru/docs/atlas/data-federation/query/sql/sqlgenerateschema/

Best,
Alexi

[rohan_dhunde]

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

[Alexi_Antonino]

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

[rohan_dhunde]

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.

[Alexi_Antonino]

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

[Levi_Lais]

This doesn’t seem to work any more. Getting:

MongoServerError[Unauthorized]: not authorized, correlationID = 17de8adfca216581fbf81911

So, my guess is the UI features were added and this isn’t supported anymore to avoid mistakes?

I went to the Data Federation page in Atlas and clicked on the “…” button in Connect/Configuration/…, then “Manage SQL Schemas” and “Generate all default schemas” and was able to get passed the DataSource.Error: The table has no visible columns and cannot be queried error.

But when I look at the data inside Excel, it’s pretty much a jumbled mess of collections with a lot of data missing. Do I have to hand stitch everything back together, or is there a way we can get collections as separate SQL tables?

Thanks!

[Alexi_Antonino]

Greetings @Levi_Lais We did add in the ability to manage the SQL Schemas from within the Atlas UI (More specifically under the Data Federation menu). This error you received usually indicates that a sql schema is not available or needs to be generated. You can do so with the mongosh commands or through the UI.

When you mention the data within Excel being different, is that different comparing Atlas SQL ODBC and BI Connector? Or are you comparing Atlas SQL ODBC to Atlas SQL ODBC since this schema update to the UI? If you would like to send me some screenshots via email, I would greatly appreciate it. We did add some functionality to the ODBC Driver, I would like to make sure the data in Excel is as expected. alexi.antonino@mongodb.com