Forum Discussion

rsbin's avatar
rsbin
Community Champion
3 years ago
Solved

M Query Parameters - X Hours

Good Afternoon, Thanks to the suggestion from ppm1 , working with M Query Parameters for the first time.  Have read through the docs and videos, but am stuck on how best to modify the Advanced Edit...
Need Help
  • rsbin's avatar
    rsbin
    3 years ago

    ppm1,

    After several unsuccessful tries at trying to create a sql query syntax, decided to go with Guy in a Cube solution and just filtered my rows instead after the query call.

    Seems to me to be a much easier solution.

    let
    Source = Sql.Database("remprexcloud.database.windows.net", "CamcoPortals"),
    dbo_v_NS63rd24hourportaldata = Source{[Schema="dbo",Item="v_NS63rd24hourportaldata"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_v_NS63rd24hourportaldata, each [timestamp] >= DateTime.LocalNow() - #duration(0,HoursParameter+5,0,0) ),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"timestamp", Order.Ascending}})
in
    #"Sorted Rows"

    Thanks again for the tips on Local Time and Duration.

    Best Regards,

     

ppm1's avatar
ppm1
Solution Sage
3 years ago

I would first get it working with a hard-coded value for the datetime concatenated into your SQL, and then use some M code to convert your dynamic parameter into a DateTime value with that format. Something like this

 

DateTime.ToText(DateTime.LocalNow() - #duration(0, yourparameter, 0, 0), "yyyy-mm ...")

 

I would put the above in a separate M step (so you can easily troubleshoot) and then concatenate that step into your SQL expression.

 

Pat