cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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

 

6 REPLIES 6
Highlighted
Memorable Member
Memorable Member

Re: Grouping sickness absence

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

 

Highlighted
Helper II
Helper II

Re: Grouping sickness absence

@Rocco_sprmnt21 

 

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

Highlighted
Memorable Member
Memorable Member

Re: Grouping sickness absence

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.

 

 

Highlighted
Helper II
Helper II

Re: Grouping sickness absence

@Rocco_sprmnt21 

 

Thanks.  This has not worked.

 

Do i need to setup a seperate table that holds the holiday information.  Also you mention in your reply that i can clean the code from the parts commented out - sorry i`m not sure what you mean here - which parts are commented out.

 

Thanks,

 

Brendan

Highlighted
Memorable Member
Memorable Member

Re: Grouping sickness absence

this is the code

 

 

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})),

    #"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 grp(List.Difference(_[Date],holidays))}})
  
    in
    #"Grouped Rows"

 

 

this is the output table

 

image.png

 

 

this is the holiday table I used:

 

image.png

 

You have to create a reeal "holiday" table.

 

 

this is the input table, that I called "Source"

 

image.png

 

PS

If this is not what you expect or it is not working, in order to help you, you must provide more detailed information of what you have done and the code and data used.

 

 

Highlighted
Helper II
Helper II

Re: Grouping sickness absence

@Rocco_sprmnt21 

 

Thanks again for your ongoing support on this.  I`m afraid i cant replicate.

 

Please see link to .pbix file below that has the source data added as a table and the holidays added as a seperate table.

 

Click here for file 

 

Please let me know fi you need more information.

 

Thanks,

 

Brendan

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors