Forum Discussion

Bfaws's avatar
Bfaws
Helper III
6 years ago

Grouping sickness absence

Hello Community,

 

I was hoping someone could help.

 

 I`m able to run a report on Sickness Absence data from one of our systems at work. The report shows a number of employees and thier sickness absence data. The data is displayed by showing a new row for each days absence. What i`m trying to do is group the consecutive days. For example we have an employee who was off sick for three days (14/01/2019 - 16/01/2019) and the report shows a new line for each day. I need to be able to group these 3 days that I can highlight as one absence. 

 

I have a query that has been written and mostly works really well.  It recognises if the absence continues from Friday to Monday and groups these together which is really important.  What it does not do to recognise public/ bank holidays or any specific site closures so if the absence falls over these the consecutive days grouping breaks and I have two instances. These closure days are likely to change slightly annually so I was thinking a seperate table that I could update as and when i needed would be the way forward but i dont know how to add this to the query.

 

Also, the input table has two columns (ResID, and Department) that are not populated onto the output table - any help on getting this to populate would be great. 

 

Sample file is below (excel file - will move to Power BI later)

 

Click here for file 

 

Please see current query below:

 

let
    fnDateList = (MyTable) =>
        let
            DateStart = List.Min(MyTable[Date]),
            DateEnd = List.Max(MyTable[Date]),
            #"List Dates" = List.Dates(DateStart, Duration.Days(DateEnd - DateStart)+1, #duration(1,0,0,0)),
            #"Converted to Table" = Table.FromList(#"List Dates", Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Ignore),
            #"Changed Type Date" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}),
            #"Merged MyTable" = Table.NestedJoin(#"Changed Type Date", {"Date"}, MyTable, {"Date"}, "Sick Day", JoinKind.LeftOuter),
            #"Expanded MyTable" = Table.ExpandTableColumn(#"Merged MyTable", "Sick Day", {"Employee Number", "Days"}, {"Employee Number", "Sick Days"}),
            #"Added Weekday" = Table.AddColumn(#"Expanded MyTable", "Weekday", each Date.DayOfWeek([Date], Day.Monday), Int64.Type),
            #"Filtered Weekends" = Table.SelectRows(#"Added Weekday", each [Weekday] < 5),
            #"Sorted Dates" = Table.Sort(#"Filtered Weekends",{{"Date", Order.Ascending}}),
            #"Grouped Rows" = Table.Group(#"Sorted Dates", {"Employee Number"}, {{"From", each List.Min([Date]), type date}, {"To", each List.Max([Date]), type date}, {"Sick Days", each List.Sum([Sick Days]), type number}}, GroupKind.Local),
            #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Employee Number] <> null and [Employee Number] <> ""),
            #"Added Dates" = Table.AddColumn(#"Filtered Rows", "Sickness Dates", each Text.Combine({Text.From([From]), if [To] > [From] then Text.From([To]) else null}," - "), type text),
            #"Selected Columns" = Table.SelectColumns(#"Added Dates",{"Employee Number", "Sickness Dates", "Sick Days"})
        in
            #"Selected Columns",
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee Number", Int64.Type}, {"ResID(T)", type text}, {"Date", type date}, {"Abs. code", type text}, {"Days", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Employee Number", "ResID(T)", "Abs. code"}, {{"Data", each fnDateList(_), type table}}),
    #"Combined Data" = Table.Combine(#"Grouped Rows"[Data]) in
    #"Combined Data"

 

 

Any help is appreciated.

 

Thanks,

 

Brendan

 

Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I haven't read your code, but see if this scheme can help you.
    A list of sick days is created for each employee; the holiday days contained in a specific table are removed from this list and then a function counts the groups of consecutive days.

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZnJbtswEIZfRdA5sEhJluxjlhZJixZBXPQS5MA6hC1UlVE5C/L2JVO0kcnJLObFi4CPQ3IW/kPd3uZaKVXlJ/mn3XbIVr+6h637c3njPnShdVEqvXS/V1fnn/2jmVL53QlB1UdR86OoBqPqV8pmZ/1us9n/o8oCneB7EDo/GKoLdANhSKuiXGJY4zEzdGubfXgyw3+wLcopdu2+1UxzOL2IwbIlQUdBIG1xEayQbdHtTQWskQXqFjW5cA+u7YMds7PHcdw9brZTN7aBQ9SsZqE+g0JnqlmjWGx56NBwd9EZV+rY1c4LYKN0xUGbwyT5iyqW1abQQPSyWSgq5hwWThrWapdQLFYs1Ifjsat1+wTYfYsLl/En+Y1Zb22fXbw829H9/dgNZljbv6kXBtW0uOjyNYHWvx/tPvva2cH8CAZYRkEp48OECHecHqEMliAfwe9f2gh1AVQ+2QAaylDBRs6Dwy+MIXoEh6OpznCmAichGkKDsygF63AZARWPySy8fPg2mrV9yS52Y98NmyCigLo1mQGBV4HkkfI1cRxqf6qdDvejNfts9ZC9/nxGwylMS9YImFjRviKd96YbbfZ91/dETuaHxxxBLwsdH5JzLh2KAtnMHY3JQYrWqC6k6VC/StZdxvL3IGwIPJDcUQXC8So+wQQrr4ooY0R0NHMRHZUsER1VKxEdnRsiWqdtelwmZXgkPGR4ms/jcJXhiTsflWgRHgsOGZ7m91gwyfC0nS/T8i2WBzI8ze/x0SzA66QiWce9iYhOydY6qUDXSQUaENciOtFjKZkaqig5nuLyFr7kEOCQDuWrOKirEsk4vCUixRSqwkvtHlw9mSG7tE7+duufvqVemd767wZw3NR46buQ095fLJ2ZcTDj/QTG7/kIFGunUVTDfTAPfqdn4cENdAMxr5gsriJQuAXbzYoLx+eYWzJv2i3Yo9U8ywvotofNAnbblslCGck2HIsO52WeZfiCi2n5nVsFpqc8DVzOMMMTqiL8jNT4uUfBqE6iYFQlUTB64FIwfuSRNHrikfTxxUSHr6rEdJKz8X6EpFGRQ9F4N0LSSf7GexGSTtpzvBMh6aQUw/sQkk7wN3i92b7B/j3khen2L9kXa+53z1NdtAziND/UdCjrL6iwN5mEYWDSTMPhpN/guz8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Employee Number" = _t, #"ResID(T)" = _t, Department = _t, Date = _t, #"Abs. code" = _t, Days = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee Number", Int64.Type}, {"ResID(T)", type text}, {"Department", type text}, {"Date", type date}, {"Abs. code", type text}, {"Days", type number}},"en-US"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Employee Number"}, {{"sickDays", each sickGrps(List.Difference(_[Date],holidays))}, {"sd", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"

     

     

     

     

    sickGrps

     

    let
    grp=(dates as list)=>
    let
    days=List.Count(dates)
    in List.Accumulate({1..days-1}, 1,(s,c)=>s+Number.From(Date.AddDays(dates{c-1},1)<>dates{c}))

in
    grp

     

     

     

    holidays

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZnJbtswEIZfRdA5sEhJluxjlhZJixZBXPQS5MA6hC1UlVE5C/L2JVO0kcnJLObFi4CPQ3IW/kPd3uZaKVXlJ/mn3XbIVr+6h637c3njPnShdVEqvXS/V1fnn/2jmVL53QlB1UdR86OoBqPqV8pmZ/1us9n/o8oCneB7EDo/GKoLdANhSKuiXGJY4zEzdGubfXgyw3+wLcopdu2+1UxzOL2IwbIlQUdBIG1xEayQbdHtTQWskQXqFjW5cA+u7YMds7PHcdw9brZTN7aBQ9SsZqE+g0JnqlmjWGx56NBwd9EZV+rY1c4LYKN0xUGbwyT5iyqW1abQQPSyWSgq5hwWThrWapdQLFYs1Ifjsat1+wTYfYsLl/En+Y1Zb22fXbw829H9/dgNZljbv6kXBtW0uOjyNYHWvx/tPvva2cH8CAZYRkEp48OECHecHqEMliAfwe9f2gh1AVQ+2QAaylDBRs6Dwy+MIXoEh6OpznCmAichGkKDsygF63AZARWPySy8fPg2mrV9yS52Y98NmyCigLo1mQGBV4HkkfI1cRxqf6qdDvejNfts9ZC9/nxGwylMS9YImFjRviKd96YbbfZ91/dETuaHxxxBLwsdH5JzLh2KAtnMHY3JQYrWqC6k6VC/StZdxvL3IGwIPJDcUQXC8So+wQQrr4ooY0R0NHMRHZUsER1VKxEdnRsiWqdtelwmZXgkPGR4ms/jcJXhiTsflWgRHgsOGZ7m91gwyfC0nS/T8i2WBzI8ze/x0SzA66QiWce9iYhOydY6qUDXSQUaENciOtFjKZkaqig5nuLyFr7kEOCQDuWrOKirEsk4vCUixRSqwkvtHlw9mSG7tE7+duufvqVemd767wZw3NR46buQ095fLJ2ZcTDj/QTG7/kIFGunUVTDfTAPfqdn4cENdAMxr5gsriJQuAXbzYoLx+eYWzJv2i3Yo9U8ywvotofNAnbblslCGck2HIsO52WeZfiCi2n5nVsFpqc8DVzOMMMTqiL8jNT4uUfBqE6iYFQlUTB64FIwfuSRNHrikfTxxUSHr6rEdJKz8X6EpFGRQ9F4N0LSSf7GexGSTtpzvBMh6aQUw/sQkk7wN3i92b7B/j3khen2L9kXa+53z1NdtAziND/UdCjrL6iwN5mEYWDSTMPhpN/guz8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Employee Number" = _t, #"ResID(T)" = _t, Department = _t, Date = _t, #"Abs. code" = _t, Days = _t]),
    days = Table.SelectColumns(Source,{"Date"}),
    #"Changed Type" = Table.TransformColumnTypes(days,{{"Date", type date}},"en-US"),

    hol= List.Transform(List.Random(50,11), each #"Changed Type"[Date]{Number.RoundDown(_*120)})

in
    hol

     

    • Bfaws's avatar
      Bfaws
      Helper III
      6 years ago

      Anonymous 

       

      Thanks for this. It sounds like it incorporates all the things I need. 

       

      Unfortunately, this is a little advanced for my Power Query knowledge.  Can you please provide some guidance on how to introduce these three steps into my query.

       

      Sorry if thats a silly question.

       

      Brendan

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        try this:

         

        let
 /*   fnDateList = (MyTable) =>
        let
            DateStart = List.Min(MyTable[Date]),
            DateEnd = List.Max(MyTable[Date]),
            #"List Dates" = List.Dates(DateStart, Duration.Days(DateEnd - DateStart)+1, #duration(1,0,0,0)),
            #"Converted to Table" = Table.FromList(#"List Dates", Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Ignore),
            #"Changed Type Date" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}),
            #"Merged MyTable" = Table.NestedJoin(#"Changed Type Date", {"Date"}, MyTable, {"Date"}, "Sick Day", JoinKind.LeftOuter),
            #"Expanded MyTable" = Table.ExpandTableColumn(#"Merged MyTable", "Sick Day", {"Employee Number", "Days"}, {"Employee Number", "Sick Days"}),
            #"Added Weekday" = Table.AddColumn(#"Expanded MyTable", "Weekday", each Date.DayOfWeek([Date], Day.Monday), Int64.Type),
            #"Filtered Weekends" = Table.SelectRows(#"Added Weekday", each [Weekday] < 5),
            #"Sorted Dates" = Table.Sort(#"Filtered Weekends",{{"Date", Order.Ascending}}),
            #"Grouped Rows" = Table.Group(#"Sorted Dates", {"Employee Number"}, {{"From", each List.Min([Date]), type date}, {"To", each List.Max([Date]), type date}, {"Sick Days", each List.Sum([Sick Days]), type number}}, GroupKind.Local),
            #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Employee Number] <> null and [Employee Number] <> ""),
            #"Added Dates" = Table.AddColumn(#"Filtered Rows", "Sickness Dates", each Text.Combine({Text.From([From]), if [To] > [From] then Text.From([To]) else null}," - "), type text),
            #"Selected Columns" = Table.SelectColumns(#"Added Dates",{"Employee Number", "Sickness Dates", "Sick Days"})
        in
            #"Selected Columns",
*/
    grp=(dates as list)=>
    let
    days=List.Count(dates)
    in List.Accumulate({1..days-1}, 1,(s,c)=>s+Number.From(Date.AddDays(dates{c-1},1)<>dates{c})),




    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee Number", Int64.Type}, {"ResID(T)", type text}, {"Date", type date}, {"Abs. code", type text}, {"Days", type number}},"en-US"),
    
    
    //#"Grouped Rows" = Table.Group(#"Changed Type", {"Employee Number", "ResID(T)", "Abs. code"}, {{"Data", each fnDateList(_), type table}}),
    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Employee Number", "ResID(T)", "Abs. code"}, {{"Data", each grp(List.Difference(_[Date],holidays))}})//,
    
    //each sickGrps(List.Difference(_[Date],holidays))
    
    //#"Combined Data" = Table.Combine(#"Grouped Rows"[Data]) 
    in
    #"Grouped Rows"//#"Combined Data"

         

         

        if it works (I don't have the exact knoledge of your data. For this reason a lot of thinks can go wrong)  , then you can clean the code from the parts commented out.