Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cathoms
Helper V
Helper V

Expand datetime range to four hour periods

Hi. I have rows in a data table with patient id, admit datetime and discharge datetime. My goal is to count patients and create other measures in four-hour intervals so I need to explode these rows out to have a row for each four hour period. Think of dividing the day into four hour shifts, like this:

ShiftPeriod
112AM-4AM
24AM-8AM
38AM-12PM
412PM-4PM
54PM-8PM
68PM-12AM

 

For example, say I have the following row for a patient encounter:

PatientIDDRGWeightAdmitDTSDchDTS
A1.811/18/24 10:17 AM1/19/24 6:15 PM
B1.782/7/24 11:37 AM2/12/24 1:49 PM

 

What I need is something like the following:

PatientIDDRGWeightAdmitDTSDchDTSCensusDTSShift
A1.811/18/24 10:17 AM1/19/24 6:15 PM1/18/24 11:59 AM3
A1.811/18/24 10:17 AM1/19/24 6:15 PM1/18/24 3:59 PM4
A1.811/18/24 10:17 AM1/19/24 6:15 PM1/18/24 7:59 PM5
A1.811/18/24 10:17 AM1/19/24 6:15 PM1/18/24 11:59 PM6
A1.811/18/24 10:17 AM1/19/24 6:15 PM1/19/24 3:59 AM1
A1.811/18/24 10:17 AM1/19/24 6:15 PM1/19/24 7:59 AM2
A1.811/18/24 10:17 AM1/19/24 6:15 PM1/19/24 11:59 AM3
A1.811/18/24 10:17 AM1/19/24 6:15 PM1/19/24 3:59 PM4
A1.811/18/24 10:17 AM1/19/24 6:15 PM1/19/24 7:59 PM5

 

And of course the same for PatientID B, C, D, etc. Any ideas?

2 ACCEPTED SOLUTIONS

Sh

 

let
    Source = {0..200},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "CensusDTS", each #datetime(2024,1,18,3,59,0)  + #duration(0,[Column1]*4,0,0),type datetime),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Shift", each Number.Mod([Column1],6)+1,Int64.Type)
in
    #"Added Custom1"

 

 

Pat

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcsxDoAgDEbhq5DOxPpXFOgGO4l7w/2voVSnl7zkM6NGkbAVrDAKSwrYFTm08a261qU4wz1oRqPuIpc3wtkB9PiBMMSXpupgPg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PatientID = _t, DRGWeight = _t, AdmitDTS = _t, DchDTS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PatientID", type text}, {"DRGWeight", type number}, {"AdmitDTS", type datetime}, {"DchDTS", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (k)=> Table.SelectRows(Sh, each k[AdmitDTS]<=[CensusDTS] and [CensusDTS]<=k[DchDTS])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"CensusDTS", "Shift"}, {"CensusDTS", "Shift"})
in
    #"Expanded Custom"

 

 

View solution in original post

This worked for me so I'm going to mark it as a solution but I actually ended up taking a different route. Here's what I did:

1. Create a discharge shift time column

= Table.AddColumn(#"Reordered Columns", "DchShiftTime", each if [DischargeTime] <= #time(3, 59, 0) then #time(3, 59, 0) else if [DischargeTime] <= #time(7, 59, 0) then #time(7, 59, 0) else if [DischargeTime] <= #time(11, 59, 0) then #time(11, 59, 0) else if [DischargeTime] <= #time(15, 59, 0) then #time(15, 59, 0) else if [DischargeTime] <= #time(19, 59, 0) then #time(19, 59, 0) else if [DischargeTime] <= #time(23, 59, 0) then #time(23, 59, 0) else null)

2. Merge that new column with discharge date to create a discharge datetime column. For each encounter this gives me a datetime for the last minute of the four-hour shift.

= Table.AddColumn(#"Added Conditional Column", "DchShiftDTS", each Text.Combine({Text.From([DischargeDT], "en-US"), Text.From([DchShiftTime], "en-US")}, " "), type datetime)

3. Determine the number of minutes between the discharge shift datetime and the admit datetime and make that an integer

= Table.AddColumn(#"Changed Type3", "IPStayMinutes", each (Duration.TotalMinutes( [DchShiftDTS]-[InpatientAdmitDTS])))

= Table.TransformColumnTypes(#"Added Custom",{{"IPStayMinutes", Int64.Type}})

4. Here is the nifty part! List all the datetimes for the admission datetime plus the number of minutes calculated in the previous step (+1 to get that last minute)

= Table.AddColumn(#"Changed Type1", "Minutes", each List.DateTimes( [InpatientAdmitDTS], [IPStayMinutes]+1, #duration(0,0,1,0)))

5. Expand that list to new rows and change the resulting column to type datetime

= Table.ExpandListColumn(#"Added Custom3", "Minutes")

= Table.TransformColumnTypes(#"Expanded Minutes",{{"Minutes", type datetime}})

6. Add a new time column based on the previous so I have just the time for each row

= Table.AddColumn(#"Changed Type2", "CensusTime", each DateTime.Time([Minutes]), type time)

7. Finally, the payoff! Filter the "CensusTime" column to just the six end of shift times, leaving me with one row per census 'moment' for an encounter

= Table.SelectRows(#"Inserted Time3", each [CensusTime] = #time(3, 59, 0) or [CensusTime] = #time(7, 59, 0) or [CensusTime] = #time(11, 59, 0) or [CensusTime] = #time(15, 59, 0) or [CensusTime] = #time(19, 59, 0) or [CensusTime] = #time(23, 59, 0))

 

The big problem is that it took a long time to load the data. With six years of data I started with 196K rows and ended up with 5.15 million rows. Five million rows isn't that significant but in between, with the calculation and expansion of the minute-by-minute rows, there would have been hundreds of millions of rows to process. If I modify this or do something similar in the future I might calculate by hour rather than minute in increase efficiency at the cost of some accuracy and precision.

View solution in original post

15 REPLIES 15
lbendlin
Super User
Super User

Shift = INT(TIMEVALUE(CensusDTS)*6)+1

Thanks. However, that answer assumes I already have the CensusDTS column. How do I get the CensusDTS from the Admit & Discharge DTS columns?

lbendlin_0-1712177563613.png

 

FYI, I noticed this earlier but forgot to mention. In your DAX approach here you aren't returning the correct shift for the last row for patient A. Not sure if that means everything is off. If the discharge datetime is 1/19/2024 6:15 PM, the Census DTS should be 1/19/2024 7:59 PM, shift 5.

Awesome! That looks promising. Do you know of a way to do this in the Query Editor?

Sh

 

let
    Source = {0..200},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "CensusDTS", each #datetime(2024,1,18,3,59,0)  + #duration(0,[Column1]*4,0,0),type datetime),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Shift", each Number.Mod([Column1],6)+1,Int64.Type)
in
    #"Added Custom1"

 

 

Pat

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcsxDoAgDEbhq5DOxPpXFOgGO4l7w/2voVSnl7zkM6NGkbAVrDAKSwrYFTm08a261qU4wz1oRqPuIpc3wtkB9PiBMMSXpupgPg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PatientID = _t, DRGWeight = _t, AdmitDTS = _t, DchDTS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PatientID", type text}, {"DRGWeight", type number}, {"AdmitDTS", type datetime}, {"DchDTS", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (k)=> Table.SelectRows(Sh, each k[AdmitDTS]<=[CensusDTS] and [CensusDTS]<=k[DchDTS])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"CensusDTS", "Shift"}, {"CensusDTS", "Shift"})
in
    #"Expanded Custom"

 

 

This worked for me so I'm going to mark it as a solution but I actually ended up taking a different route. Here's what I did:

1. Create a discharge shift time column

= Table.AddColumn(#"Reordered Columns", "DchShiftTime", each if [DischargeTime] <= #time(3, 59, 0) then #time(3, 59, 0) else if [DischargeTime] <= #time(7, 59, 0) then #time(7, 59, 0) else if [DischargeTime] <= #time(11, 59, 0) then #time(11, 59, 0) else if [DischargeTime] <= #time(15, 59, 0) then #time(15, 59, 0) else if [DischargeTime] <= #time(19, 59, 0) then #time(19, 59, 0) else if [DischargeTime] <= #time(23, 59, 0) then #time(23, 59, 0) else null)

2. Merge that new column with discharge date to create a discharge datetime column. For each encounter this gives me a datetime for the last minute of the four-hour shift.

= Table.AddColumn(#"Added Conditional Column", "DchShiftDTS", each Text.Combine({Text.From([DischargeDT], "en-US"), Text.From([DchShiftTime], "en-US")}, " "), type datetime)

3. Determine the number of minutes between the discharge shift datetime and the admit datetime and make that an integer

= Table.AddColumn(#"Changed Type3", "IPStayMinutes", each (Duration.TotalMinutes( [DchShiftDTS]-[InpatientAdmitDTS])))

= Table.TransformColumnTypes(#"Added Custom",{{"IPStayMinutes", Int64.Type}})

4. Here is the nifty part! List all the datetimes for the admission datetime plus the number of minutes calculated in the previous step (+1 to get that last minute)

= Table.AddColumn(#"Changed Type1", "Minutes", each List.DateTimes( [InpatientAdmitDTS], [IPStayMinutes]+1, #duration(0,0,1,0)))

5. Expand that list to new rows and change the resulting column to type datetime

= Table.ExpandListColumn(#"Added Custom3", "Minutes")

= Table.TransformColumnTypes(#"Expanded Minutes",{{"Minutes", type datetime}})

6. Add a new time column based on the previous so I have just the time for each row

= Table.AddColumn(#"Changed Type2", "CensusTime", each DateTime.Time([Minutes]), type time)

7. Finally, the payoff! Filter the "CensusTime" column to just the six end of shift times, leaving me with one row per census 'moment' for an encounter

= Table.SelectRows(#"Inserted Time3", each [CensusTime] = #time(3, 59, 0) or [CensusTime] = #time(7, 59, 0) or [CensusTime] = #time(11, 59, 0) or [CensusTime] = #time(15, 59, 0) or [CensusTime] = #time(19, 59, 0) or [CensusTime] = #time(23, 59, 0))

 

The big problem is that it took a long time to load the data. With six years of data I started with 196K rows and ended up with 5.15 million rows. Five million rows isn't that significant but in between, with the calculation and expansion of the minute-by-minute rows, there would have been hundreds of millions of rows to process. If I modify this or do something similar in the future I might calculate by hour rather than minute in increase efficiency at the cost of some accuracy and precision.

Read about Table.Buffer  and its siblings.  Or do this all in DAX.

Thanks so much for all your help!

This is helpful, thanks much. I've come up with an alternative solution but yours is much more elegant. However, I'm not entirely sure what is going on in that M code and would like to understand so that I can incoporate it into my query.

  1. What does " (k)=> " do exactly? I understand the Table.SelectRows portion.
  2. In the Sh table, why 0 to 200, i.e. 200 rows? Is that based on the date range in my sample data? My actual data go back to 2018-01-01. Does that Sh table need to have many more rows then? If so, should I hard code it as you have or add some dynamic table that counts based on actual dates in the patient encounters table?

When I try to incorporate this into my query that custom column on the patient encounters table returns as a function

cathoms_3-1712240054942.png

 

and that function asks for a parameter

cathoms_2-1712239840029.png

 

If you have the time could you explain what this is doing and how I might fix it? 

 

1.  "(k)"  -  that's a long story.  When I started to learn about context in Power Query I saw an example that used k,  and then I used it too, and have used it ever since.  It means "current context"  and you can use whatever string you like.

2. yeah, whatever range you need.

 

Note that your formula contains an extra "each"  - you can either have the "each"  (but nesting them gets nasty very quickly) or the explicit reference via (k)=>  , but not both.  Remove the first "each"  and you get a table instead of a function.

 

If you want to learn Power Query, read Ben Gribaudo's primer.

Ach! Stupid cut and paste error!

Done for the day but I will try that out tomorrow. Thanks so much!

cathoms
Helper V
Helper V

Could you share a straightforward solution? All I could find is a way to expand date ranges to create rows for each day between the admit & discharge dates. I haven't seen anything on how to break out ranges by hour or grouped hours.

lbendlin
Super User
Super User

That seems to be straightforward . What have you tried and where are you stuck?

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.