Power Query - Next Work Day

[pacofq]

Hi evryone,

I have a date table structured like this

[Date]

2017-01-01

2017-01-02

...

I'm trying to generate a calculated column that return the "next work (open) day" relative to my [Date] column

I managed to generate a column that identifies if the date is a WORKDAY or a NOWORKDAY by verifying if its a saturday, a sunday or if the date is a Holiday (by looking in my holiday table (T_FERIES)), but i'm not able to calculate the next open day

let
    Source = Excel.Workbook(File.Contents("\\DATES.xlsx"), null, true),
    T_DATES_Table = Source{[Item="T_DATES",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(T_DATES_Table,{{"Date", type date}}),
    #"MERGE - T_FERIES" = Table.NestedJoin(#"Changed Type",{"Date"},T_FERIES,{"JourCal"},"T_FERIES",JoinKind.LeftOuter),
    #"EXPAND - Feries" = Table.ExpandTableColumn(#"MERGE - T_FERIES", "T_FERIES", {"JourCal"}, {"Ferie"}),
    #"Added Conditional Column" = Table.AddColumn(#"EXPAND - Feries", "WORKDAY", each if [Ferie] <> null or Date.DayOfWeek(Date.From([Date])) = 0 or Date.DayOfWeek(Date.From([Date])) = 6 then "NOWORKDAY" else "WORKDAY"),
    #"Added Custom" = Table.AddColumn(#"Added Conditional Column", "MinWorkDay", each List.Min(Table.SelectRows(#"Added Conditional Column"[Date], [WORKDAY] = "WORKDAY"),type number))
in
    #"Added Custom"

Here's what my table looks like right now. I'm trying to fill the 4th column with the "Next Work Day"

If anyone could help it would be much appreciated!!

Thanks in advance!

[Zubair_Muhammad]

@pacofq

Try adding this new custom column

=let mydate=Date.DayOfWeek(Date.From([Date])) in
if mydate=5 then Date.AddDays([Date],3)
 else 
if List.AnyTrue({mydate=0,mydate=6})=false 
then 
Date.AddDays([Date],1)
 else null

= Table.AddColumn(#"Added Custom", "Custom", each let mydate=Date.DayOfWeek(Date.From([Date])) in
if mydate=5 then Date.AddDays([Date],3) else if List.AnyTrue({mydate=0,mydate=6})=false then Date.AddDays([Date],1) else null)