Forum Discussion
Pass a value to ODBC data access command on refresh
Hi,
I access a very large datasetthat is too big to load into PBI in one go ( lots of histroical data). The data is held in Google BigQuery, so we use an ODBC connection to access it, and "where" to do some pre-filtering to reduce the data set.
Source = Odbc.Query("dsn=MyData", "SELECT * FROM core-appliance-214519.production.socket_readings_v202106 where (device_id='ide_production_142' ) and read_at>= '2022-2-7T00:00:01'"),
At the moment I have to keep going in to the Advanced Editor and manually change the data paramter to select a new period.
What I want to do is either enter a date on a sheet in PBI and then refresh, or have a pop-up come up on refresh to ask for the date range. Also, if i could calculate the date field to be "last 7 days", my weekly auto refresh would work without intervention from me.
Any ideas?
Thanks
Wayne
This can be done with the query parameter in Power Query Editor. Have you tried it?
Create a parameter query (Power Query) (microsoft.com)
Parameters - Power Query | Microsoft Docs
And you can use the following M code to get the date 7 days ago. I have formatted it to be consistent with the datetime format in your SQL statement.
= DateTime.ToText(Date.AddDays(DateTime.LocalNow(),-7),[Format="yyyy-M-d'T'00:00:01"])So you could try
Source = Odbc.Query("dsn=MyData", "SELECT * FROM core-appliance-214519.production.socket_readings_v202106 where (device_id='ide_production_142' ) and read_at>= ' & DateTime.ToText(Date.AddDays(DateTime.LocalNow(),-7),[Format="yyyy-M-d'T'00:00:01"]) & '"),Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
3 Replies
waynewoodhead , try like
Source = Odbc.Query("dsn=MyData", "SELECT * FROM core-appliance-214519.production.socket_readings_v202106 where (device_id='ide_production_142' ) and read_at>= '" & Date.AddDays(DateTime.Date(DateTime.LocalNow()),-7) &"'" ),
if required use format on text
Hi Amitchandak,
I tried your suggestion and get this error
Clearer text
Source = Odbc.Query("dsn=MyData", "SELECT * FROM core-appliance-214519.production.socket_readings_v202106
where (device_id='ide_production_142' or device_id='ide_production_144' ) and read_at>= '" & Date.AddDays(DateTime.Date(DateTime.LocalNow()),-7) &"'""),
#"Sorted Rows" = Table.Sort(Source,{{"read_at", Order.Ascending}}),Any suggestions?
thanks
Wayne
This can be done with the query parameter in Power Query Editor. Have you tried it?
Create a parameter query (Power Query) (microsoft.com)
Parameters - Power Query | Microsoft Docs
And you can use the following M code to get the date 7 days ago. I have formatted it to be consistent with the datetime format in your SQL statement.
= DateTime.ToText(Date.AddDays(DateTime.LocalNow(),-7),[Format="yyyy-M-d'T'00:00:01"])So you could try
Source = Odbc.Query("dsn=MyData", "SELECT * FROM core-appliance-214519.production.socket_readings_v202106 where (device_id='ide_production_142' ) and read_at>= ' & DateTime.ToText(Date.AddDays(DateTime.LocalNow(),-7),[Format="yyyy-M-d'T'00:00:01"]) & '"),Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
