Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to Solution.
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,
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
@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,
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |