Hi @Adolfo_Adrian Thanks so much for your question and welcome to the community! Sometimes when a collection(s) can’t be loaded or previewed within Power Query it is because of a SQL schema issue. We do a very quick scan and sample 1 document to build the SQL schema so this could be problematic when you have polymorphic data throughout your source collection. You can fix this by going into MongoDB Shell and running a command to generate the SQL Schema, with a larger sample size. You can do this for each collection or for all collections within your federated database. Here is our docs page for this info as well: https://www.mongodb.com/docs/atlas/data-federation/query/sql/schema-management/
Here are some instructions to help guide you:
- Connect to your Federated DB within MongoDB Shell.
- Type: use admin
- Hit: enter
- Type: db.runCommand({sqlGenerateSchema: 1, sampleNamespaces: [“datalake.testDL”], sampleSize: 1000, setSchemas: true})
- Hit: enter
In the below command, datalake = the name of my virtual database and testDL is the viratul collection. You could also generate a SQL Schema for all collections within a Federated DB instance virtual database with a wildcard:
db.runCommand({sqlGenerateSchema: 1, sampleNamespaces: [“datalake.*”], sampleSize: 1000, setSchemas: true})
But this error has me thinking that your collection name(s) may contain a period or dollar sign? Can you see if that is the case? If that is the case, we are looking into a fix that may correct this soon, but for now a work around would be to use a manually created Federated DB (not a SQL Quickstart) and when creating the virtual database and collection names within the Federated DB instance make sure to avoid periods/dots and dollar signs.
Let me know if you need any assistance.
Best,
Alexi