cancel
Showing results for 
Search instead for 
Did you mean: 

Query Folding with CosmosDB and incremental Refresh

Hi,

 

I am trying to perform an incremental refresh for a data set from cosmosDB. My data contains a JSON document with embedded documents. Example table `tbl_profile`

Document 1 sample:

 

 

{
  id:1,
  name:[{
     "first":"John",
     "last":"Doe"
  }],
 children:[
      {
       "name":"BabyJohn1",
       "age":2,
      },
     {
       "name":"BabyJohn2",
       "age":5,
      }
 ]
}

 

 

 

I followed the following steps:

Step -1 : Retrieved the data. The source appeared as following in forumula bar

Source = DocumentDB.Contents("https://cosmosdb-dummy.documents.azure.com:443/", "db_dummy")

 

Step -2: After loading the tbl_Profile, I see the documents appear as rows of records

Nikeshman_0-1656928184142.png

Step-3 And after Expanding, when i right clicked on the Applied step, the 'View native Query ' is greyed out, which to my knowledge means the query cannot be fold.

Nikeshman_1-1656928337912.png

 

As a result of unfolded query, I am unable to set the incremental refresh and loading takes a long time during refreshes.

 

So my Question is

1. Does cosmosDB support Query Folding?

2. If it does, how may I achieve query folding in power bi with cosmosDB. I have also tried using value.nativeQuery() function as follows but it displayed an error

 

 

 

let
  Source =  DocumentDB.Contents("https://dummy-cosmosdb.documents.azure.com:443/", "db_dummy"),
  NativeResults = Value.NativeQuery(
    Source, 
    "select * from tbl_profile",
     null, 
     [enable_folding=true])
in
    NativeResults

 

 

Nikeshman_2-1656928749493.png

Does anyone have an idea on what I am doing wrong?

 

I thank you in advance for all your help

Status: Investigating

Hi @Nikeshman ,

 

Most data sources that have the concept of a query language support query folding. These data sources can include relational databases, OData feeds (including SharePoint lists), Exchange, and Active Directory. However, data sources like flat files, blobs, and web typically do not.

 

You could refer to Power Query query folding 

 

Best Regards,
Community Support Team _ Caitlyn

 

Comments
v-caitlyn-mstf
Community Support
Status changed to: Investigating

Hi @Nikeshman ,

 

Most data sources that have the concept of a query language support query folding. These data sources can include relational databases, OData feeds (including SharePoint lists), Exchange, and Active Directory. However, data sources like flat files, blobs, and web typically do not.

 

You could refer to Power Query query folding 

 

Best Regards,
Community Support Team _ Caitlyn