Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Filtered query to Cosmos DB for large data set

Hello,   I am storing a large IoT data set in a Cosmos DB collection. At the moment I am using Power BI to exploit and display the information stored in this collection.   The more the collection...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hello Yuliana,

 

I found an mid-way solution using parameters. I defined "initDate" and "finishDate" as text-type parameters.

 

Then I modified the query so it looks like:

 

= DocumentDB.Contents("https://{comos-db-name}.documents.azure.com:443/", "{db-name}", "{collection-name}", [Query = "SELECT * FROM Document WHERE Document.date BETWEEN '"&initDate&"' AND '"&finishDate&"'"])

 

However, I am still wondering if it can be done playing with dates built-in functions.

mtzvictor's avatar
mtzvictor
Frequent Visitor
7 years ago

I found the solution in this post

https://community.powerbi.com/t5/Service/Azure-DocumentDB-and-slow-refresh/m-p/115509#M18257

 

 

 

Basically you have to:

 

Go to advanced Editor and do something as below. The function #duration(7,0,0,0) is where you subtract days from UTCNOW()

In this case the first value indicate 7 days, the next one hours, and so forth.

 

let 

querystring= "SELECT * FROM c WHERE ( c.YOURTimeVAR >= '" & DateTimeZone.ToText(DateTimeZone.UtcNow() - #duration(7,0,0,0),"yyyy-MM-ddTHH:mm:ss.000Z" ) & "')",
Source = DocumentDB.Contents("URLTOYOURCOSMOSDB", "DBNAME", "COLLECTION", [Query = querystring])

in

Source

 

 

Hope this helps

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Greetings,

     

    This was never worked in my case and  i have used the below query to fetch the data from power bi

     

    SELECT * FROM c WHERE (c.StartDate BETWEEN '" & DateTimeZone.ToText(DateTimeZone.UtcNow() - #duration(7,0,0,0),"yyyy-MM-ddTHH:mm:ss.000Z" ) & "' AND '" & DateTimeZone.ToText(DateTimeZone.UtcNow(),"yyyy-MM-ddTHH:mm:ss.000Z") & "')

     

     

    In order to test this query on cosmosdb query explorer. I did run this query and below is the error:

     

    image.png

     

     

    There is no response from microsoft yet after opening at ticket.