Forum Discussion

sebastian62460's avatar
sebastian62460
Copper Contributor
Sep 22, 2023

Excel power query custom column

Hello I have this formula in custom power query column. I want to make a list of times in a 5 minutes interval. The start is in [Zaciatok Procedury] column and the end is in [Koniec Procedruy]. For example i Have set the date and time in [Zaciatok Procedury] to 18. 9. 2023 9:05:00 and in the [Koniec Procedury] to 18. 9. 2023 10:30:00. But this formula crates me a list from 9:05 (thats awesome), but it ends at 10:40, which is not right. The formula is an attached screenshot.

Thanks for your help

developer
excel
Formulas and Functions

2 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Sep 23, 2023

    sebastian62460 

    If count number of intervals as Duration.TotalMinutes( Duration.From( [End]-[Start] ) )/5+1 it works as well.

    All together

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    addPeriods = Table.AddColumn(
        Source,
        "List",
        each List.Times(
            Time.From( [Start] ),
            Duration.TotalMinutes( Duration.From( [End]-[Start] ) )/5+1,
            #duration(0,0,5,0))),
    expandPeriods = Table.ExpandListColumn(addPeriods, "List")
in
    expandPeriods
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Sep 23, 2023

    sebastian62460 

    Duration.Minutes doesn't calculate the number of minutes between the two times! It merely calculates the difference in the minute parts of the times. In your case, 30 - 5 = 25. When you expand the list produced with your code you will get a list of 25 times, starting at 9:05 and ending at 11:05 with 5 minute intervals. 

     

    To get the 17 times that you expect to get, try this code in stead:

    List.Times(Time.From([Start]),(Number.From ([End])-Number.From ([Start]))/5*1440, #duration(0,0,5,0))

     

    Example attached.

Resources