Help with Absence Data Status

[Syndicate_Admin]

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.

[Syndicate_Admin]

Thank you so much Vijay, this worked perfectly, apologies for the delay in confirming that it had.

[Vijay_A_Verma · Accepted solution]

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"