M Query Parameters - X Hours

[rsbin]

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 Editor Query.

The premise is for the User to be able to select the range of Hours (1 - 24) to view in the Report, based on a timestamp field.

let
    Source = Sql.Databases("remprexcloud.database.windows.net"),
    CamcoPortals = Source{[Name="CamcoPortals"]}[Data],
    dbo_v_NS63rd24hourportaldata = CamcoPortals{[Schema="dbo",Item="v_NS63rd24hourportaldata"]}[Data],
    #"Renamed Columns" = Table.RenameColumns(dbo_v_NS63rd24hourportaldata,{{"direction", "Direction"}}),
    #"Replaced inbound" = Table.ReplaceValue(#"Renamed Columns","inbound","Inbound",Replacer.ReplaceText,{"Direction"}),
    #"Replaced outbound" = Table.ReplaceValue(#"Replaced inbound","outbound","Outbound",Replacer.ReplaceText,{"Direction"}),
    #"Added Custom" = Table.AddColumn(#"Replaced outbound", "ChassisLength", each Text.Length([chassisNumber])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "UnitLength", each Text.Length([unitNumber] )),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Chassis/Unit", each [chassisNumber] & "/" & [unitNumber]),
    #"Sorted Rows" = Table.Sort(#"Added Custom2",{{"timestamp", Order.Descending}})

in
    #"Sorted Rows"

This is my code thus far. 

timestamp	Direction
2022-11-02T12:20:59.2070000	Outbound
2022-11-02T12:05:51.3330000	Outbound
2022-11-02T12:04:19.4370000	Inbound
2022-11-02T12:04:17.9830000	Inbound
2022-11-02T12:03:10.8970000	Inbound
2022-11-02T12:02:54.0200000	Inbound
2022-11-02T12:00:04.9600000	Inbound

My Parameter is called "HoursParameter".   The table I will bind it to is called "HourSlicer".

I believe what I am looking for is the code to filter my query such that:

[timestamp] >= DATEADD(HH, HoursParameter*-1, [timestamp] ).  Not sure if this should be SQL code or M code?

Grateful for any assistance.

Kind Regards,

[ppm1]

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]

@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 · Accepted solution]

@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,