Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Nsr_1975
Regular Visitor

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

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

1 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.

View solution in original post

5 REPLIES 5
jfieck
Regular Visitor

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.

sittell_0-1716293881597.png

 

Best regards,

Lisa.

v-binbinyu-msft
Community Support
Community Support

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors