Forum Discussion
Solved
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 ...
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 ) )
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/