Forum Discussion

waynewoodhead's avatar
waynewoodhead
Helper I
4 years ago
Solved

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" t...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi waynewoodhead 

     

    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.

amitchandak's avatar
amitchandak
Super User
4 years ago

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

waynewoodhead's avatar
waynewoodhead
Helper I
4 years ago

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