Forum Discussion
Create calculated table to generate new rows as per column value
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
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 ) )
4 Replies
- amustafa
Solution Sage
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:
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/ - amustafa
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 ) )
