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

 

 

  • rsbin's avatar
    rsbin
    Community Champion
    3 years ago

    ppm1 ,

    Good Morning.  Thanks for the response.  It took me a while, but eventually figured that is the path to take.  Haven't quite got there yet, as I was encountering some unrelated issues with the data set.

    Will continue down this path today, and with trial and error, I believe I will get there.

    Thanks again for confirming the DateTime.ToText piece.  This was something that was giving me some uncertainty.

    Kind Regards,

     

    • rsbin's avatar
      rsbin
      Community Champion
      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,