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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Bfaws
Helper III
Helper III

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
Anonymous
Not applicable

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

 

@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
Not applicable

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.

 

 

@Anonymous 

 

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

Anonymous
Not applicable

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.

 

 

@Anonymous 

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors