Incremental refresh for MongoDB database

[KareemMostafa12]

Will be happy if someone can help me 😔

i'm using MongoDB connected with PBI with BI connector to using ODBC DSN and every thing is working fine till now

recently i tried to setup incremental refresh by making Parameters <RangeStart> and <RangeEnd> then create filter for date/time columns filter-->betwwen and select in between dates. Once i close and Load the query the table gives error loading message "OLE DB or ODBC error: [DataSource.Error] ODBC: ERROR [HY000] [MySQL][ODBC 1.4(a) Driver][mysqld-5.7.12 mongosqld v2.14.5]This command is not supported in the prepared statement protocol yet." 

Waiting my hero😀

[lbendlin]

You are supposed to replace your code, not append to it.

I used the [createdAt]  field as an example - modify as needed.

[KareemMostafa12]

Hi Ibendlin!

Yes my target column is also [CreatedAt] same as you mentioned in your code and i replaced with this code but when i click Done it gives this error message

  

[lbendlin]

Good, making progress. What format does MongoDB expect for date or datetime fields?  Is "2023-01-17"  enough?

[KareemMostafa12]

 it is date/time field firmat as shown in the photo

[lbendlin]

that's not what I meant. How is MongoDB expecting it to be formatted?  Can you show a sample SQL query?

[KareemMostafa12]

Is it what you mean

 

[lbendlin]

Yes. Change your Power Query to 

DateTime.ToText(RangeStart,[Format="yyyy-MM-dd'T'HH:mm:ss'Z'"])

and the same for RangeEnd.

[KareemMostafa12]

where can i add this statement exactly?

[lbendlin]

let
    Source = Odbc.Query("dsn=MongoDB", "select * from groupshops where [createdAt]>='" & DateTime.ToText(RangeStart,[Format="yyyy-MM-dd'T'HH:mm:ss'Z'"]) & "' and [createdAt]<'" & DateTime.ToText(RangeEnd,[Format="yyyy-MM-dd'T'HH:mm:ss'Z'"]) & "'")
in
    Source

[KareemMostafa12]

I think i'm doing something wrong 

[lbendlin]

Can you get the SQL query to work with the same datetime values?  Maybe the expected format is different, or without single quotes etc.

[KareemMostafa12]

I will contact database team and to try changing SQL format and will feedback you. thanks for your patience 😊

[Anonymous]

Hi @KareemMostafa12 ,

Is there any update on the issue?

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[KareemMostafa12]

Still not solved i'm trying to find a solution with my database team to chage date formate 

[lbendlin]

Are you running a custom query or are you applying the RangeStart/RangeEnd filter later in the Power Query transforms?

[KareemMostafa12]

I'm just applying RangeStart/RangeEnd filter later in the power query

[lbendlin]

May be that the query folding is not working properly for MongoDB.  Apply the parameters directly in the ODBC call.

NOTE:  one of the parameters must be inclusive and the other must be exclusive.  Otherwise you end up with gaps or overlaps between partitions.

[KareemMostafa12]

Is it possible to show me how to apply the parameters directly in the ODBC call also can you inform me how can i make inclusive and exclusive parameters. Actually i'm not professional in PBI

[lbendlin]

Show a sanitized version of your Power Query code.  You can see the code by selecting  "Advanced Editor"

[KareemMostafa12]