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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

this version of function splitWard is valid even if the original table is not well ordered (in case where the first row of group with same ID is not the "Avd 1" one)

 

 

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])),
    pos=List.PositionOf(tab[Location], "Avd 1"),
    newTab=Table.ReplaceRows(tab, pos,1,{tab{pos}&[End=minFoll], tab{pos}&[Start=maxPrec]}),
    #"Sorted Rows" = Table.Sort(newTab,{{"Start", Order.Ascending}})
in
    #"Sorted Rows"
in splitWard