Looking to find time currency by minute

[Awollet33]

Hi All - 

I have the datetime of the stop and start times of the surgieries going on at a hospital. I am trying to find out how many surgeries are happening in the hospital by minute. It has to be by minute. Does anyone have any experince working with a datetime range and finding concurrency by minute? I was hoping I could expand the table by the datetime range to create a row for every minute.

Any and all ideas are appreciated!!! Thanks for the help - Adam

[Awollet33]

Hi @bcdobbs ,

Thank you so much for your help and detailed answer. I am just am seeing it now and I am going to look through the information you provided and it give it a try. Super excited to try and crack this problem as it will be a great help to our hospital. 

Thanks,

Adam

[bcdobbs]

Not done it at the minute grain but often do expand day start/end to a table at the day grain which is done using:

TableExpanded = 
    GENERATE (
        OriginalTable,
        DATESBETWEEN('Calendar'[Date], OriginalTable[Start Date], OriginalTable[End Date] )
    )

I think if you create a time table at the minute grain you sould be able to the same to expand it.

If you can provide some demo data we could try and work it out.

[bcdobbs]

Actually there's no MINUTESBETWEEN function so need to give that bit more thought. Thinking generateseries might help but need to have a play.

[bcdobbs · Accepted solution]

Have a look at this demo

In steps:

1) Started with a basic table:

2) In power query broke apart the date and times:

3) Changed the original datetimes to decimals.

4) Created custom columns multipling the start and end decimals by 24*60. (1 day equals 1 so this returns a whole number of minutes since 30 December 1899. )

Full M:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI00jcw1DcyMDJSMLC0MjDAEDI2UIrViVYyAkkYIyQsQBIoQoYGIO0gtcZoamHmIqk1BKuNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SurgeryId = _t, #"DateTime Start" = _t, #"DateTime End" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"SurgeryId", Int64.Type}, {"DateTime Start", type datetime}, {"DateTime End", type datetime}}),
    AddDateStart = Table.AddColumn(ChangedType, "Date Start", each DateTime.Date([DateTime Start])),
    AddDateEnd = Table.AddColumn(AddDateStart, "Date End", each DateTime.Date([DateTime End])),
    AddTimeStart = Table.AddColumn(AddDateEnd, "Time Start", each DateTime.Time([DateTime Start])),
    AddTimeEnd = Table.AddColumn(AddTimeStart, "Time End", each DateTime.Time([DateTime End])),
    ChangedType2 = Table.TransformColumnTypes(AddTimeEnd,{{"DateTime Start", type number}, {"DateTime End", type number}}),
    AddMinuteStartStamp = Table.AddColumn(ChangedType2, "MinuteStartStamp", each [DateTime Start]*24*60),
    AddMinuteEndStamp = Table.AddColumn(AddMinuteStartStamp, "MinuteEndStamp", each [DateTime End] * 24 * 60),
    ChangedType3 = Table.TransformColumnTypes(AddMinuteEndStamp,{{"MinuteStartStamp", Int64.Type}, {"MinuteEndStamp", Int64.Type}}),
    RemovedOtherColumns = Table.SelectColumns(ChangedType3,{"SurgeryId", "Date Start", "Date End", "Time Start", "Time End", "MinuteStartStamp", "MinuteEndStamp"})
in
    RemovedOtherColumns

6) Use the following DAX to create a table at minute grain:

ExpandedTable = 
    GENERATE (
        OriginalTable,
        VAR CurrentStart = OriginalTable[MinuteStartStamp]
        VAR CurrentEnd = OriginalTable[MinuteEndStamp]
        RETURN
            GENERATESERIES( CurrentStart, CurrentEnd, 1 )
    )

7) Rename the VALUE column to "Minute Stamp".

I'd like to be able to do that all in PowerQuery but can't quite make it work at the moment. Will update if I sort it.

[bcdobbs]

Version entirely in PowerQuery which I think is tidier.

Just needed to add an extra column in power query with following:

List.Numbers([MinuteStartStamp], [MinuteEndStamp]-[MinuteStartStamp] + 1, 1)

That column can then expand to new rows.