Forum Discussion

JoaoFidalgo94's avatar
JoaoFidalgo94
Regular Visitor
1 year ago
Solved

New Date without considering Holidays or Weekends

Hi everyone!   I need a new column with date after 15 networkdays without considering Holidays that i have that table on my power query. How can I do it? I did it but it doesn't ignores me the days...
  • dufoq3's avatar
    1 year ago

    Hi JoaoFidalgo94, check this:

     

    Output

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrYwMlfSUTIy0Tc00DcyMDJRitWBixqY6hsaQUVjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Conjunto = _t, #"Data Recep ARM" = _t]),
        Feriado = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLQNzTSNzIwMlHSUXJLLcpMTMlXitUBShjjkjDBJWGKS8IMl4Q5DgljXK4yNsQhYWCoD0RACVNkiVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t, Tipo = _t]),
        Holidays = List.Buffer(List.Transform(Feriado[Data], Date.From)),
        StepBack = Source,
        ChangedType = Table.TransformColumnTypes(StepBack,{{"Data Recep ARM", type date}, {"Conjunto", Int64.Type}}),
        Ad_Plus15WorkingDays = Table.AddColumn(ChangedType, "Plus 15 working days", each 
            [ a = List.Skip(List.Dates([Data Recep ARM], 40, #duration(1,0,0,0))),
              b = List.Select(a, (x)=> (not List.Contains(Holidays, x)) and Date.DayOfWeek(x, Day.Monday) < 5)
            ][b]{14}, type date )
    in
        Ad_Plus15WorkingDays