Forum Discussion
Help with Absence Data Status
I am new to Power Query and starting to see the benefits of using it. I have a a data issue that I have tried to research using YouTube and forums but to no avail and I hope someone here can assist me.
Issue: I have to report on staff off on long term absence. I have to categorise them based on their status i.e. are they newly off (commenced), have they returned (returned) or are they off the whole reporting month. I have screen shot a sample data with notes to explain what I am attempting to achieve.
As each reporting month has different end dates etc I am totally confused how to achieve this in Power Query.
Any help would be greatly appreciated.
Use below Query where ReportMonth is a parameter to store your reporting month.
Solution Excel uploaded to https://1drv.ms/x/s!Akd5y6ruJhvhuUUKb3_p1vZ7Cp5C?e=eHE291
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Personnel Number", type text}, {"Absence Start Date", type date}, {"Absence End Date", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Status", each if [Absence End Date]=Date.EndOfMonth(#date(Date.Year([Absence End Date]),ReportMonth,1)) or (Date.Month([Absence Start Date])<>ReportMonth and Date.Month([Absence End Date])<>ReportMonth) then "All Month" else if Date.Month([Absence Start Date])=ReportMonth then "Commenced" else if [Absence End Date]<=Date.AddDays(Date.EndOfMonth(#date(Date.Year([Absence End Date]),ReportMonth,1)),-1) and [Absence End Date]>=#date(Date.Year([Absence End Date]),ReportMonth,1) then "Returned" else null) in #"Added Custom"
2 Replies
Use below Query where ReportMonth is a parameter to store your reporting month.
Solution Excel uploaded to https://1drv.ms/x/s!Akd5y6ruJhvhuUUKb3_p1vZ7Cp5C?e=eHE291
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Personnel Number", type text}, {"Absence Start Date", type date}, {"Absence End Date", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Status", each if [Absence End Date]=Date.EndOfMonth(#date(Date.Year([Absence End Date]),ReportMonth,1)) or (Date.Month([Absence Start Date])<>ReportMonth and Date.Month([Absence End Date])<>ReportMonth) then "All Month" else if Date.Month([Absence Start Date])=ReportMonth then "Commenced" else if [Absence End Date]<=Date.AddDays(Date.EndOfMonth(#date(Date.Year([Absence End Date]),ReportMonth,1)),-1) and [Absence End Date]>=#date(Date.Year([Absence End Date]),ReportMonth,1) then "Returned" else null) in #"Added Custom"Thank you so much Vijay, this worked perfectly, apologies for the delay in confirming that it had.
