Not all collumns showing in Power BI (Using Mongo DB Atlas beta connector)

Hello im triying the new Power BI connector. All my collections are showing, but some collections have missing information/columns when opened in the Power Query editor. What can i do to fix this?

Hello @Santiago_De_la_Pena_Miranda and welcome to the MongoDB Community! I am pretty sure that the underlying SQL schema just didn’t sample enough documents to represent some of these “missing” fields/columns. If you regenerate the SQL Schema, you can make sure these fields get represented. Here are some instructions:

Here is the command that you would run, of course you would change the db instance name from “datalake” to th name of your virtual db name and you can also change the sample size.
db.runCommand({sqlGenerateSchema: 1, sampleNamespaces: [“datalake.*”], sampleSize: 1000, setSchemas: true})

Hello Alexi_Antonino - I successfully implemented the above steps as mentioned by you. Unfortunately, I still don’t get to see the entire columns on Power BI. It would be really helpful if you could help me resolve the issue as I’m struck on this issue from many days.
Appreciate your help !

Below is the actual tables on MongoDB - I have around 74 fields with few nested objects.

Below is the list of columns that PowerBI has pulled ( around 15 columns )…rest is missing

Hi there @Srinivas_Jayaram - a few questions. When you ran the sqlGenerateSchema command, did the output/results list all of the columns in your collection? Did you change the sample size to a larger number to make sure all of the fields were sampled?

Here is what the output of the sqlGenerateSchema looks like (this is just a portion of my collection- but this shows an array field called items and the fields within items) - you should see all fields present in this schema:

If you run the sqlGenerateSchema command and do not see all of the fields you expect, please run this again and increase the sample size (you can up this to 20K even).

And my last question, did you enable Atlas SQL with the Quickstart? or did you manually create a federated database? If you manually created a federated database, I would want to make sure that you have all of the cluster collections separated into virtual collections. Sometimes users miss this:

Let me know if this is helpful.

Hi Alexi Antonino,

Thank you so much for the reply. I’m now able to get the desired field on PowerBI.

Step1 : I have increased the sample size to 10k
Step 2 : I have enabled Atlas SQL with Quickstart.

It worked for me !!! thank you so much for all your help. Appreciate it !!!

Thanks,
Srini

I’m so glad this works for you! Best to you and your Power BI adventures:)