Forum Discussion

ToreVingare's avatar
ToreVingare
Frequent Visitor
5 years ago

Stop row based on next row, process mining

Hello!   I'm working on a process mining dashboard. In this dashboard I'm mapping a patient journey in our hospital. The process looks like this. Patient arrives at a ward -> Is rushed to surgery -...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZG9DsIwDIRfJercSLaT0MRbRzYkJJaqG4ixqPxIvD1JW7VpUiDjxd/ZZzdNgUVZEKCTQBK0QGAAkb64xgi07Cs3aurXWWDRlluuCn+Z+hLFEKjjs79e+vcXH2Lzz8eyNl46dPeH7G75mFu2ExNLbpT2p3ogaP5WEjEQysWSChmpWi0hZ6Iug0Tot52FTjjyy1OxRAI1a7eEzKlxHBtJCkI3tZsmpIGZbRGkP5Bn9GyDKAHDHoazLKk2GJtIxGiyVClHDFUiGaZVqvYD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Start = _t, End = _t, Location = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Start", type datetime}, {"End", type datetime}, {"Location", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Ids", each splitWard(_)}}),
    #"Expanded Ids" = Table.ExpandTableColumn(#"Grouped Rows", "Ids", {"ID", "Start", "End", "Location"}, {"Ids.ID", "Ids.Start", "Ids.End", "Ids.Location"})
in
    #"Expanded Ids"

 

 

the splitWard function:

 

let
splitWard=(tab)=>
let
    minFoll=List.Min(List.Select(tab[Start], each _>tab{[Location="Avd 1"]}[Start])),
    maxPrec=List.Max(List.Select(tab[End], each _<tab{[Location="Avd 1"]}[End])),
    newTab=Table.ReplaceRows(tab, 0,1,{tab{0}&[End=minFoll], tab{0}&[Start=maxPrec]}),
    #"Sorted Rows" = Table.Sort(newTab,{{"Start", Order.Ascending}})
in
    #"Sorted Rows"
in splitWard

a possible result:

 

 

try this