Convert Start Time & End Time in multiple Rows in M Code

[lync0056]

Hi, 

I need assistance with converting 1 timesheet row into multiple 15 minute interval rows.  As an example 1 row is showing as start time column 1am to end time column 9am. I need that row to change to 32 duplicate rows. Except 1 column would have all the different 15 min intervals

30/08/2019 1.00AM

30/08/2019 1.15AM

30/08/2019 1.30AM

30/08/2019 1.45AM

30/08/2019 2.00AM

30/08/2019 2.15AM

30/08/2019 2.30AM

Etc.

Is this possible to do this with M Code? Or even SQL?

Thank you

Cameron

[v-lid-msft · Accepted solution]

Hi @lync0056 ,

We can meet your requirement in Power Query Editor.

1. Firstly, we have a table.

2. then we add a column to calculate the interval counts.

Duration.TotalMinutes([EndTime]-[StartTime])/15)

3. Then we add another list to contain all the time between this time, you can read this document about this function.

List.DateTimes([StartTime],[Custom]+1,#duration(0, 0, 15, 0))

(BTW, you can also change the Custom1 column to the formula Duration.TotalMinutes([EndTime]-[StartTime])/15))

4. expand the list

5. remove the column you do not want and rename the new column

the Complete M Query is here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYiMDQ0t9C31jAwVDKwMDIFJIzEURtkQIx+pEKzmBJLHqK0DWZ4hsXGwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Value = _t, StartTime = _t, EndTime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Value", Int64.Type}, {"StartTime", type datetime}, {"EndTime", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Duration.TotalMinutes([EndTime]-[StartTime])/15),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.DateTimes([StartTime],[Custom]+1,#duration(0, 0, 15, 0))),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.1",{"StartTime", "EndTime", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.1", "Time"}})
in
    #"Renamed Columns"

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lync0056]

This was exactly what I needed and worked perfectly. Thank you. @v-lid-msft