Create calculated table to generate new rows as per column value

[Jramirej]

Hi Team,

I am searching for one solution over the internet but am unable to find one. Your support is highly appreciated.
I have data like below
Appointment Date & Time data format is DateTime

Task	Appointment Date & Time	Duration (Mins)
Meeting with Mr A	14-02-2024 02:55	10
Eye Checkup	01-03-2024 07:00	5

As you find the 1st Task for 10 Minutes, that means for 10 mins time the doctor is not available for another task. So I have to generate one calculated table like below

Task	Appointment Date & Time	Duration (Mins)
Meeting with Mr A	14-02-2024 02:55	1
Meeting with Mr A	14-02-2024 02:56	1
Meeting with Mr A	14-02-2024 02:57	1
Meeting with Mr A	14-02-2024 02:58	1
Meeting with Mr A	14-02-2024 02:59	1
Meeting with Mr A	14-02-2024 03:00	1
Meeting with Mr A	14-02-2024 03:01	1
Meeting with Mr A	14-02-2024 03:02	1
Meeting with Mr A	14-02-2024 03:03	1
Meeting with Mr A	14-02-2024 03:04	1
Eye Checkup	01-03-2024 07:00	1
Eye Checkup	01-03-2024 07:01	1
Eye Checkup	01-03-2024 07:02	1
Eye Checkup	01-03-2024 07:03	1
Eye Checkup	01-03-2024 07:04	1

Logic - 

Thank you in Advance

[amustafa · Accepted solution]

Alright, DAX calculated table is as following. My sample pbix file has been updated.

Expanded DrTasks = 
GENERATE(
    DrTasks,
    ADDCOLUMNS(
        GENERATESERIES(0, DrTasks[DurationMin] - 1, 1),
        "ExpandedDateTime", DrTasks[AppointmentDateTime] + TIME(0, [Value], 0),
        "DurationMinExpanded", 1
    )
)

[Jramirej]

Works perfectly
Thanks @amustafa for your time and support.

[Jramirej]

Hi @amustafa 
It is working in Power Query. Thanks for the code. 

But I have a direct query table, so I can't do the transformation at the Power Query level. For that reason, I am looking for DAX Code

[amustafa]

I can only do this in Power Query. Here' my M Code. Adjust the source and column names.

let
    Source = Excel.Workbook(File.Contents("C:\Users\aliom\OneDrive\Power BI Samples\Dr Appt Schedule\Sample.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Renamed Columns" = Table.RenameColumns(Table1_Table,{{"Appointment Date & Time", "AppointmentDateTime"}, {"Duration (Mins)", "DurationMin"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Task", type text}, {"AppointmentDateTime", type datetime}, {"DurationMin", Int64.Type}}),
    
    // Custom function to generate a list of datetime values
    GenerateDateTimeList = (startDateTime as datetime, duration as number) as list =>
        List.Transform({0..duration-1}, each startDateTime + #duration(0, 0, _, 0)),

    // Apply the custom function to each row
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Time List", each GenerateDateTimeList([AppointmentDateTime], [DurationMin])),

    // Expand the custom column to new rows
    #"Expanded Time List" = Table.ExpandListColumn(#"Added Custom", "Time List"),
    
    // Optionally, adjust the final column types
    #"Final Types" = Table.TransformColumnTypes(#"Expanded Time List",{{"Time List", type datetime}}),
    #"Added Custom1" = Table.AddColumn(#"Final Types", "Duration in Mins", each 1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Duration in Mins", Int64.Type}})
in
    #"Changed Type1"

Download my sample files:

Dr Appt Schedule

If I answered your question, please mark this thread as accepted and Thums Up!
Follow me on LinkedIn:
https://www.linkedin.com/in/mustafa-ali-70133451/