Dynamic load with date variable on SQL query

[Anonymous]

Hi,

I want to query an sql table by odbc in power bi, hardcoded date works fine.

I want to have a dynamic date,  prefered by parameter, used in variable  

current works fine

Let

     Source= Odbc.Query("{};dsn={}","select #(lf)#(tab)*#(lf) from data WHERE Datum >= EVAL('date(""2020/01/01"")') ") 

in
    Source

1. Test( delivers zerro records in result)

??- Can you help me out??

Let

    StartDate=DateTime.Date(Date.StartOfYear(Date.AddWeeks(DateTime.LocalNow(),-1))),

    Source= Odbc.Query("{};dsn={}","select #(lf)#(tab)*#(lf) from data  WHERE Datum >= EVAL('date(""$StartDate$"")') ")

in
    Source

2. If that works i would prefere something like 

?? is this possible and how

parameter string setting =>  Data_StartDate = DateTime.Date(Date.StartOfYear(Date.AddWeeks(DateTime.LocalNow(),-1)))

Let

    StartDate=#Data_StartDate,

    Source= Odbc.Query("{};dsn={}","select #(lf)#(tab)*#(lf) from data  WHERE Datum >= "$StartDate$" ")

in
    Source

[Anonymous]

Hi Amit,

Thanks for reply, it is not give me the answer i'm looking for. I look for the right syntax to build in my script, and somewhere somehow it is not working. And i cant find it. see my example.  But overal a good link. 

[v-yiruan-msft]

Hi @Anonymous ,

You can update the codes in your Advanced Editor as follow, the part with red font is updated one:

Let

    StartDate= Date.ToText(DateTime.Date(Date.StartOfYear(Date.AddWeeks(DateTime.LocalNow(),-1))), "yyyy/MM/dd"),

    Source= Odbc.Query("{};dsn={}","select #(lf)#(tab)*#(lf) from data  WHERE Datum >= EVAL('"&StartDate&"') ")

in
    Source

In addition, you can refer the content in following links to achieve it:

Dynamic parameter for SQL to filter last year

Dynamic M query parameters in Power BI Desktop (preview)

Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

[Anonymous]

tnx,  I had tried this before , unfortunately this stil the wrong date format. strange enough. 

the outcome in the system database query  it gives back  - select  * from data WHERE Datum >= EVAL('2020-01-01') 

i get back 

DataSource.Error: ODBC: ERROR [HY000] [DI][ODBC Driver] SQLExecDirect: remote dive request:6:31: Type error in expression `value("Datum") >= 2020 - 1 - 1`: Arguments are not convertible to a common type
Details:
DataSourceKind=Odbc
DataSourcePath=cbase=/datamarts/DataMart_Ritpunctualiteit/DataMart_Ritpunctualiteit.cbase;dsn=zightbi-prd
OdbcErrors=[Table]

any further sugestions?

[v-yiruan-msft]

Hi @Anonymous ,

What's the data type of field Datum? If it is date type, please try to update the codes in Advanced Editor as below. 

Let

    StartDate= Date.ToText(DateTime.Date(Date.StartOfYear(Date.AddWeeks(DateTime.LocalNow(),-1))), "yyyy/MM/dd"),

    Source= Odbc.Query("{};dsn={}","select #(lf)#(tab)*#(lf) from data  WHERE Datum >= '"&StartDate&"' ")

in
    Source

If it is working, could you please accept this post as Solution? It will help other members in the community find the solution easily if they face the similar problem with you. Thank you.

Best Regards

[amitchandak]

@Anonymous , refer if this can help

https://www.biinsight.com/power-bi-desktop-query-parameters-part-1/