Power BI - Cosmos DB V2 Connector - Heterogenous Schema

Hi jjaywatts ,

First, regarding the question “Power BI builds its schema definition by reading the first 1000 rows, it’s ommitting some attributes that are present in the rows beyond 1000.” You can change this in Power Query to be based on entire data set by clicking here:

Regarding "there doesn't seem to be a way to provide a sql query using the Cosmos DB V2 connector.", you can right-click the operation you are performing in Power Query and see if the option "View Native Query" is available. As shown in the screenshot I provided, if the "View Native Query" button is grayed out, it means that the data source cannot convert the query in Power Query to a sql query:

You could probably use the Azure Cosmos DB Data Migration Tool (dt.exe) to export the data to a JSON file, making sure you include all the necessary properties, and then import that JSON file into Power BI. This way you can control the schema that Power BI sees, but keep in mind that this approach doesn't use DirectQuery and is better suited for smaller datasets.

In addition, regarding the Data Factory you mentioned, this is already a problem within the scope of Fabric. Perhaps you can post on the Data Factory forum, and more professional engineers will come to answer your questions.
Data Factory forums - Microsoft Fabric Community

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.