Combining IF statement with date calculation in Power Query

Question

Hi everyone,

Thank you in advance for your assistance.

I am trying to add a custom column to calculate if a complaint (Row entries) have exceeded 28 days from when it is received; there is a date received column. There is a STATUS column that indicates whether a complaint has been completed or still remains Open.

I am interested in monitoring complaints that remain Open for more than 28 days from date they were received. I guess the custom column should calculate number of days for STATUS Open ones and for Completed ones, I guess it can perhaps populate word OK or something meaningful.

I am open to suggestions, hope the above makes sense. This is my first time posting a question, thank you.

cheers,

James

Answer 1

Hi @James

No idea how the custom column column should be named => XYZ below
No idea what you want when [Status] = "Open" & 28d have passed => Late below

let  
// Table for demo.  
    Source = Table.FromColumns(  
        {  
            {#date(2021,7,1), #date(2021,7,5), #date(2021,6,30), #date(2021,8,1)},  
            {"Complete","Open","Complete","Open"}  
        },  
        type table [Date received = date, Status = text]  
    ),  
//  
    todaysDate = DateTime.Date(DateTime.LocalNow()),  
    NewColumn = Table.AddColumn(Source, "XYZ", each  
        if [Status] = "Open" and Duration.Days(todaysDate - [Date received]) >= 28  
        then "Late"  
        else "OK",  
        type text  
    )  
in  
    NewColumn  

Any problem to adapt this to your actual scenario please post a picture of your table + another one showing what you expect. Thanks