Splitting the time attribute in increments of 15min until the specific time range is achieved in BI

[Emil_ansarijose]

Hello Community,

I'm currently not able to increment the time attribute in the multiples of 15 min.

To explain the scenario briefly:

Existing data set:

AppointmentID	StartDate	EndDate	StartTime	EndTime
1	16-Aug-22	16-Aug-22	12:00:00	14:00:00
2	20-Jan-22	21-Jan-22	23:00:00	00:15:00

Required data set:

AppointmentID	StartDate	EndDate	StartTime	EndTime
1	16-Aug-22	16-Aug-22	12:00:00	12:15:00
1	16-Aug-22	16-Aug-22	12:15:00	12:30:00
1	16-Aug-22	16-Aug-22	12:30:00	12:45:00
1	16-Aug-22	16-Aug-22	12:45:00	13:00:00
1	16-Aug-22	16-Aug-22	13:00:00	13:15:00
1	16-Aug-22	16-Aug-22	13:15:00	13:30:00
1	16-Aug-22	16-Aug-22	13:30:00	13:45:00
1	16-Aug-22	16-Aug-22	13:45:00	14:00:00
2	20-Jan-22	20-Jan-22	23:00:00	23:15:00
2	20-Jan-22	20-Jan-22	23:15:00	23:30:00
2	20-Jan-22	20-Jan-22	23:30:00	23:45:00
2	20-Jan-22	21-Jan-22	23:45:00	00:00:00
2	20-Jan-22	21-Jan-22	00:00:00	00:15:00

How do I get this done in powerBI using DAX or transformation?

[tamerj1 · Accepted solution]

Hi @Emil_ansarijose 

For dax solution plesae refer to attached file

Table 2 = 
SELECTCOLUMNS ( 
    GENERATE ( 
        'Table',
        GENERATESERIES ( 'Table'[StartTime], 'Table'[EndTime] - TIME ( 0, 15, 0 ), TIME ( 0, 15, 0 ) )
    ),
    "AppointmentID", [AppointmentID],
    "StartDate", [StartDate],
    "EndDate", [EndDate],
    "StartTime", [Value],
    "EndTime", [Value] + TIME ( 0, 15, 0 )
)

[Emil_ansarijose]

Thank you works good!