Forum Discussion

pacofq's avatar
pacofq
Advocate I
7 years ago

Power Query - Next Work Day

 

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!

 

 

 

 

Need Help

1 Reply

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    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)