Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
Solved! Go to 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.
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.
Hi,
I don't know though how to define an sql query for V2 and have also a problems with this connector as it doesn't parse arrays well, I wanted to tell you that there is a way to expand the data for V1. Before loading the data for V1 press "Transform" and then - press the arrows to expand the data. Then there will be columns. I hope it could help you.
Best regards,
Lisa.
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.
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
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.