Escaping, truncating and combining dates.

[Anonymous]

I have a call to an API that includes a "Start" and "End" time. The entire call looks like this.

start=2023-12-11T12%3A00%3A00.000Z&end=2023-12-13T12%3A00%3A00.000Z&timeline=time-series-classification&device=ame_FH800SX_1_e82a2d

First question I have is that when I call a Date time it is not escaped, IE looks like this "2024-02-12T07:47:40.3346910" instead of "2024-02-12T07%3A47%3A40.3346910"....and there is no "Z" at the end. Is there an easy way to escape dates and times? I was looking at several things that *might* do it but this seems like something that should be easier than what I was looking at.

Second question. Is there a way to truncate and combine dates and times. For instance I will be calling several devices and combining the data into a single table. I want to have two tables a "Today" table and the "Last 120 day" table. The reason I want to do this is that I want to regularly be updating "Today's" data and do not want to have to pull the last 120 days each time.

A "Day" in this case will be first and second shift so I want to start the day at 6AM the day prior and start a new day at 6AM today. Since we are running second shift we have people working from 4PM to 2AM, sometime longer and that time I want to include in "Yesterday's" time.

So my my relatively novice language what I want for one table is " If time is less than 6AM Start=Today()-1:6AM End = Now, If time is greater than 6AM Start = Today():6AM End=Now"

What I want for the other table is " If time is less than 6AM Start=Today()-121:6AM End = Today()-2:6AM, If time is greater than Start=Today()-120:6AM End = Today()-1:6AM"

I expermineted with "AddDays" and that works but I can't seem to figure out how to do the rest.

Any help is greatly appreciated.

Thank You

[Anonymous]

Hi @Anonymous ,

To properly escape dates and times for API calls, you can use the Uri.EscapeDataString method in C# or a similar URL encoding function in the language you are using. This method will convert characters that are not allowed in a URL into percent-encoded format. For example:

string unescapedDateTime = "2024-02-12T07:47:40.3346910";
string escapedDateTime = Uri.EscapeDataString(unescapedDateTime);
// Add the 'Z' to indicate UTC time if necessary
escapedDateTime += "Z";

Uri.EscapeDataString(String) Method (System) | Microsoft Learn

For creating specific "Today" and "Last 120 days" tables, you can use Power Query in Power BI to manipulate date and time values. Here's a high-level approach to achieve this:

Use the DateTime.LocalNow() function to get the current date and time.
Use the DateTime.AddHours() function to adjust the time as needed.
Use conditional logic to determine which time frame to use based on the current time.
Here's an example of how you might define the "Today" table in Power Query M function:

let
    CurrentTime = DateTime.LocalNow(),
    StartOfToday = if Time.Hour(CurrentTime) < 6 then DateTime.Date(DateTime.AddHours(CurrentTime, -18)) else DateTime.Date(CurrentTime),
    EndOfToday = CurrentTime,
    TodayTable = ... // Your logic to create the table
in
    TodayTable

For the "Last 120 days" table, you would adjust the logic to calculate the start and end times based on the current time and then filter your data accordingly.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

So I managed to muddle thru this and came up with this. It seems to work but can anyone tell me if I'm breaking in cardinal rules?

let

    CurrentHour = Time.Hour(DateTime.LocalNow()),
    CurrentDate2 = DateTime.LocalNow(),
    CurrentHourYes = if CurrentHour > 6 then CurrentDate2 else Date.AddDays(CurrentDate2,-1),
    CurrentStartTime =  Date.StartOfDay(CurrentHourYes) +#duration(0,6,0,0),
    StartTimeText = DateTime.ToText(CurrentStartTime,"yyyy-MM-dd"&"T"&"HH:mm:ss.fff"),
    EndTimeText = DateTime.ToText(DateTime.LocalNow(),"yyyy-MM-dd"&"T"&"HH:mm:ss.fff"),
   StartTime = Uri.EscapeDataString(StartTimeText),
   EndTime = Uri.EscapeDataString(EndTimeText),

Param1 = "start="&StartTime&"Z",
Param2 = "&end="&EndTime&"Z",
Param3 = "&timeline=time-series-classification",
Param4 = "&device=ame-INTEGREX200-IV-9c1185",