Forum Discussion
JoaoFidalgo94
1 year agoRegular Visitor
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...
- 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
JoaoFidalgo94
1 year agoRegular Visitor
Thank you dufoq3, i think this works fine for the porpose!
dufoq3
1 year agoCommunity Champion
You're welcome.