Forum Discussion

Navaneetharaju_'s avatar
Navaneetharaju_
Helper II
2 years ago
Solved

Next_run_dates based on the occurrences

Hi , 

 

This request regards the task report, finding the next_run_date and time for task based on the repeat_every, and end_occurences ,


Task_name = Task 1
utc_start_date = 27-Feb-24
utc_start_time = 03:00:00 PM
Repeat_every = 15

Repeat_by = minutes 

End_Occurences = 2000


I have created the series in power query
Series = {1..[End_Occurences]
It's created the 2000 rows for the one task and i have added the query function to create the next_run_times based on the occurences and repeat every field
next_run_time = [utc_start_time]+#duration(0,0,[repeat_every]*[Series],0)

I'm getting the results of every 15 mins of time like, 03:00:00 PM, 03:15:00 PM, 03:30:00 PM till the end occurrences count the times are repeating.
I want to find the next_run_dates using power query,
Because task_start_date (27-FEB-2024) and start_time (03:00:00 PM) it's running from the time onwards,  after the 23:59:59 the the tasks will running on 28-Feb-2024 .

need to find the next dates column along with the next_run_time

scenario:
If select 27 th feb in my slicer it should show count of the task which runs on that day,  if i select 28th feb 24 it should show only the count of task will run on that.

Please help me to get this done 

Need Help
Show and Tell
  • jgeddes's avatar
    jgeddes
    2 years ago

    I am not 100% sure this is what you are looking for. But it might get you pointed in the right direction.
    If you are looking for a result similar to...

    I was able to achieve this with the following code...

    let
    Source = 
    Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkkszlYwVNJRMjDSNzLXNzIwMgFyDE2tDAzANJDIzcwrLUktBrKMDICisbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Task_name = _t, utc_start_date = _t, utc_start_time = _t, Repeat_every = _t, Repeat_by = _t, End_Occurences = _t]),
    #"Changed Type" = 
    Table.TransformColumnTypes(
        Source,
        {
            {"Task_name", type text}, 
            {"utc_start_date", type date}, 
            {"utc_start_time", type time}, 
            {"Repeat_every", Int64.Type}, 
            {"Repeat_by", type text}, 
            {"End_Occurences", Int64.Type}
        }
    ),
    #"Inserted Merged Column" = 
    Table.AddColumn(
        #"Changed Type", 
        "utc_start_datetime", 
        each 
        DateTime.From(
            Text.Combine(
                {
                    Text.From([utc_start_date], "en-US"), 
                    Text.From([utc_start_time], "en-US")
                },
                " "
            )
        ), 
        type datetime
    ),
    #"Added Custom" = 
    Table.AddColumn(
        #"Inserted Merged Column", 
        "Repeat_duration", 
        each 
        if [Repeat_by] = "days" 
            then #duration([Repeat_every], 0, 0, 0) 
            else 
        if [Repeat_by] = "hours" 
            then #duration(0, [Repeat_every], 0, 0) 
            else 
        if [Repeat_by] = "minutes" 
            then #duration(0, 0, [Repeat_every], 0) 
            else #duration(0, 0, 0, [Repeat_every]), 
        type duration
    ),
    Custom1 = 
    Table.AddColumn(
        #"Added Custom", 
        "_seriesTable", 
        each 
        List.DateTimes([utc_start_datetime], [End_Occurences], [Repeat_duration]), 
        type list
    ),
    #"Removed Columns" = 
    Table.RemoveColumns(
        Custom1,
        {"utc_start_date", "utc_start_time", "Repeat_every", "Repeat_by", "End_Occurences", "utc_start_datetime", "Repeat_duration"}
    ),
    #"Expanded _seriesTable" = 
    Table.ExpandListColumn(
        #"Removed Columns", 
        "_seriesTable"
    ),
    #"Inserted Date" = 
    Table.AddColumn(
        #"Expanded _seriesTable", 
        "Occurence_date", 
        each DateTime.Date([_seriesTable]), 
        type date
    )
in
    #"Inserted Date"

2 Replies

  • jgeddes's avatar
    jgeddes
    Super User
    2 years ago

    I am not 100% sure this is what you are looking for. But it might get you pointed in the right direction.
    If you are looking for a result similar to...

    I was able to achieve this with the following code...

    let
    Source = 
    Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkkszlYwVNJRMjDSNzLXNzIwMgFyDE2tDAzANJDIzcwrLUktBrKMDICisbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Task_name = _t, utc_start_date = _t, utc_start_time = _t, Repeat_every = _t, Repeat_by = _t, End_Occurences = _t]),
    #"Changed Type" = 
    Table.TransformColumnTypes(
        Source,
        {
            {"Task_name", type text}, 
            {"utc_start_date", type date}, 
            {"utc_start_time", type time}, 
            {"Repeat_every", Int64.Type}, 
            {"Repeat_by", type text}, 
            {"End_Occurences", Int64.Type}
        }
    ),
    #"Inserted Merged Column" = 
    Table.AddColumn(
        #"Changed Type", 
        "utc_start_datetime", 
        each 
        DateTime.From(
            Text.Combine(
                {
                    Text.From([utc_start_date], "en-US"), 
                    Text.From([utc_start_time], "en-US")
                },
                " "
            )
        ), 
        type datetime
    ),
    #"Added Custom" = 
    Table.AddColumn(
        #"Inserted Merged Column", 
        "Repeat_duration", 
        each 
        if [Repeat_by] = "days" 
            then #duration([Repeat_every], 0, 0, 0) 
            else 
        if [Repeat_by] = "hours" 
            then #duration(0, [Repeat_every], 0, 0) 
            else 
        if [Repeat_by] = "minutes" 
            then #duration(0, 0, [Repeat_every], 0) 
            else #duration(0, 0, 0, [Repeat_every]), 
        type duration
    ),
    Custom1 = 
    Table.AddColumn(
        #"Added Custom", 
        "_seriesTable", 
        each 
        List.DateTimes([utc_start_datetime], [End_Occurences], [Repeat_duration]), 
        type list
    ),
    #"Removed Columns" = 
    Table.RemoveColumns(
        Custom1,
        {"utc_start_date", "utc_start_time", "Repeat_every", "Repeat_by", "End_Occurences", "utc_start_datetime", "Repeat_duration"}
    ),
    #"Expanded _seriesTable" = 
    Table.ExpandListColumn(
        #"Removed Columns", 
        "_seriesTable"
    ),
    #"Inserted Date" = 
    Table.AddColumn(
        #"Expanded _seriesTable", 
        "Occurence_date", 
        each DateTime.Date([_seriesTable]), 
        type date
    )
in
    #"Inserted Date"