--  GENERATESERIES returns a list of values given the first
--  value, the last and the step.
EVALUATE 
    GENERATESERIES ( 1, 10, 1 )
    
EVALUATE 
    GENERATESERIES ( 5, 20, 5 )
    
EVALUATE 
    GENERATESERIES ( 0, 1, 0.1 )

--  GENERATESERIES works with date and time too, 
--  because dates are numbers.
EVALUATE 
    GENERATESERIES ( 
        DATE ( 2021, 1, 28 ),
        DATE ( 2021, 2, 3 ),
        1
    )
ORDER BY [Value]

EVALUATE
SELECTCOLUMNS (
    GENERATESERIES (
        TIME ( 0, 0, 0 ),
        TIME ( 23, 59, 59 ),
        TIME ( 2, 30, 0 )
    ),
    "Time", FORMAT ( [Value], "hh:mm:ss" )
)

EVALUATE
//Time Dimension 
//Working Hours 9:00am to 5:00pm 
VAR Opening = 9
VAR Closing = 17
//Time Slot 
VAR TimeSlot = 30
VAR Offset = ( TimeSlot / 2 )
RETURN
    GENERATE (
        GENERATESERIES ( 0, 1439, 1 ),
        // 24 hours/day * 60 minutes/hour = 1,440 - 1 (initial time 12am) 
        VAR __Minutes = [Value]
        VAR __Time =
            TIME ( 0, __Minutes, 0 )
        // MROUND: Rounds Value to the nearest multiple of TimeSlot
        VAR __MinutesRounded =
            MROUND ( ( __Minutes + Offset ), TimeSlot )
        VAR __StartTimeSlotRounded =
            TIME ( 0, __MinutesRounded - TimeSlot, 0 )
        VAR __EndTimeSlotRounded =
            TIME ( 0, __MinutesRounded, 0 )
        RETURN
            ROW (
                    "Hour", HOUR ( __Time ),
                    "Minute", MINUTE ( __Time ),
                    "Hour Minute 24h", FORMAT ( __Time, "hh:mm" ),
                    "Hour Minute 12h", FORMAT ( __Time, "hh:mm AM/PM" ),
                    "30 Minute Slot Start", FORMAT ( __StartTimeSlotRounded, "hh:mm" ),
                    "30 Minute Slot End", FORMAT ( __EndTimeSlotRounded, "hh:mm" ),
                    "30 Minute Slot", FORMAT ( __StartTimeSlotRounded, "hh:mm" ) & "-"
                        & FORMAT ( __EndTimeSlotRounded, "hh:mm" ),
                    "Working Hours", IF (
                        ( __Minutes / 60 ) >= Opening
                            && ( __Minutes / 60 ) <= Closing,
                        "Yes",
                        "No"
                    ),
                    "Time", __Time,
                    "TimeSlot Start", __StartTimeSlotRounded,
                    "TimeSlot End", __EndTimeSlotRounded
            )
    )

-- Covert a list of items in a string
-- into a table with one row for each item
--
--  PATHLENGTH determines the number of items
--  GENERATESERIES iterates the items
--  PATHITEM extract the Nth item
EVALUATE
VAR list = "123|456|789|764"
VAR _length =
    PATHLENGTH ( list )
VAR Result =
    SELECTCOLUMNS (
        GENERATESERIES ( 1, _length ),
        -- Use TEXT instead of INTEGER 
        -- to get a list of strings
        "Item", PATHITEM ( list, [value], INTEGER )
    )
RETURN
    Result