cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hussain_onmodus
New Member

ODBC Data source - Date Parameter not working

Hi, 

I have taken data from ODBC Bigquery DSN and trying to add a query over it. The query works fine when I give it fixed date values for the date range. 

However It doesn't work when I am trying to add a Date Parameter to it to dynamically change the date range. 

Here is how I have defined my date paramater. 

and here is the query for the odbc data source:

 

Odbc.Query("dsn=Google BigQuery", "#(lf)SELECT#(lf)    DISTINCT #(lf)    client_id,#(lf)    domain,#(lf)    company,    #(lf)FROM#(lf)  `bq_project.bq_dataset.bq_table` #(lf)WHERE#(lf)   FORMAT_DATE('%d/%m/%Y', date)  <= '" &endDate& "'#(lf)    and source != 'direct'#(lf) and  not REGEXP_CONTAINS(source, 'paypal' )#(lf)    AND DATE >= '2020-12-16'")

 

 
It returns Expression.Error: We cannot apply operator & to types Text and Date.

Please note that the datatype of date column in the datasource is date and not text. 

Not sure what am I doing wrong here. Please help me with it. 

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

@hussain_onmodus , the issue has nothing to do with the dataset; but only with your M code. It seems the data type of endDate is date. You might want to try to subsitute your orignal code &endDate& with & Date.ToText(endDate, "ddMMyyyy") &

View solution in original post

1 REPLY 1
CNENFRNL
Community Champion
Community Champion

@hussain_onmodus , the issue has nothing to do with the dataset; but only with your M code. It seems the data type of endDate is date. You might want to try to subsitute your orignal code &endDate& with & Date.ToText(endDate, "ddMMyyyy") &

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors