Forum Discussion

Anett_R's avatar
Anett_R
Frequent Visitor
2 years ago
Solved

Subtract working days from date column

Hello everybody,   I have the following issue:  I have a date from which I have to subtract the shipping time (in working days) to determine the planned shipping day.     In this example,...
Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago

    Anett_R this function should solve your problem in PQ

        wd = (delivery_date, days) => 
        if days = 0 
            then delivery_date 
            else @wd(
                Date.AddDays(delivery_date, -1), 
                if List.Contains({0, 6}, Date.DayOfWeek(delivery_date, Day.Monday)) 
                then days 
                else days - 1
            )
dufoq3's avatar
dufoq3
Community Champion
2 years ago

Hi Anett_R, different solution without recursive function:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLUNzDTNzIwMlHSUTJVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, #"shipping time (working days)" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"date", type date}, {"shipping time (working days)", Int64.Type}}),
    Ad_ShippingDate = Table.AddColumn(ChangedType, "shipping date", each 
        [ a = [#"shipping time (working days)"],
          b = List.Dates(Date.AddDays([date], -(a + Number.RoundUp(a/7)*2)), 3, #duration(1,0,0,0)),
          c = List.Select(b, (x)=> not List.Contains({5,6}, Date.DayOfWeek(x, Day.Monday))){0}?
        ][c], type date)
in
    Ad_ShippingDate