Azure Cosmos DB V2 Connector for Power BI - Missing Query option - Reg

[Nsr_1975]

Hi,

  I am trying to connect to cosmos db transactional store from power BI using V2 (Beta). Though it supports Direct query option, i could not find an option to pass a query like "select * from c where c.id=xxxx". 

without the query option it is initially fetching upto 1000 records which is causing very slow.. Even if i apply a filter using parameter, it is filtering only after fetching the initial 1000+ records. 
Can some one help with 

    a) How to pass a query to the connector in V2

    b) Is there a detailed documentation available for V2 connector ?
 Mquery in V1: 

    let
       Source = DocumentDB.Contents("https://xxxxxxxx-cosmosdb.documents.azure.com:443/", "xxxxxDB", "xx_container", [Query = "select * from c where c.id=""xxxxx"""]),

 Mquery in V2:

    Source = CosmosDB.Contents("https://xxxxxxxxx-cosmosdb.documents.azure.com:443/", [NUMBER_OF_RETRIES="1", ENABLE_AVERAGE_FUNCTION_PASSDOWN=null, ENABLE_SORT_PASSDOWN_FOR_MULTIPLE_COLUMNS=null]),
a_Database = Source{[Name="xxxxx",Kind="Database"]}[Data],
#"a_Schema" = a_Database{[Name="xxxxx",Kind="Schema"]}[Data],
#"a_Table" = #"a_Schema"{[Name="xxxxx",Kind="Table"]}[Data]

Thanks,

Nsr

[jfieck]

Hello.  I know this is closed but I am still not able to see anywhere in the docs referenced here where to go on the Azure Cosmos DB v2(beta) connector to enter a sql statement.  The v1 connector shows the "SQL Statement" dialog when you select "Get Data" but the v2 connector is not.  I checked all the links here and there is nothing that I can see showing where to go to specify a sql query like "select * from c".   I have not found anyting in CosmosDB.Contents docs that show if putting it there in PowerQuery is the way to go.

I find it odd that v1 shows "SQL Statement" but v2 doesn't..  Is there any other doc with instruction on call a custom sql statement through the v2 connector.  

Reason I need v2 is that v1 is not parsing the JSON into tabular format they way v2 does.  The records just show as "Record" but not the real data.  v2 will parse out the json into data columns operable in the report.

[v-binbinyu-msft]

Hi @Nsr_1975 ,

For more information about Azure Cosmos DB V2Connector, please refer this document:  Power Query Azure Cosmos DB v2 (Beta) - Power Query | Microsoft Learn

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nsr_1975]

Hi v-binbinyu,

Thanks for the reply. I am actually looking for the detailed options available with cosmosdb.contents()  the detailed documentation for the connector.

Thanks,

Nsr

[v-binbinyu-msft · Accepted solution]

Hi @Nsr_1975 ,

Please refer this document:  CosmosDB.Contents - Function | Power Query How

Hope it is helpful for you.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.