Custom function "not valid due to the current state of the object" but only when using AddColumn?

[jcountryman]

I'm trying to write a custom function to count the number of manufacturing days (M days) before or after a given date— similar to the behavior of Excel's WORKDAY.INTL() function. It works on a single value (see the step #"Single Test"), but not when used with Table.AddColumn (see the step #"Add END_DATE").

Is there a workaround?

This is my AddMDays function:

let
    AddMDays = (
        start_date as date,
        m_days_to_move as duration,
        holidays as list,
        optional off_days as nullable list
    ) => let
        off_days = if off_days = null
            then {Day.Saturday, Day.Sunday} //default weekend
            else off_days,
        forward_or_backward = if Number.From(m_days_to_move) < 0
            then -1 //count backwards
            else 1 //count forwards
    in List.Last(
        List.Generate(
            ()=> [
                COUNT=Number.Abs(Number.From(m_days_to_move)),
                DATE=start_date
            ], //initial value
            each [COUNT] >= 0, //if true, continue
            each let
                next_date = Date.AddDays(
                    [DATE],
                    forward_or_backward
                ),
                reduce_count = if List.Contains( //if a holiday
                        holidays, 
                        next_date
                    ) 
                    or List.Contains( //or a day off
                            off_days,
                            Date.DayOfWeek(next_date)
                        )
                    then 0 //do not reduce count
                    else 1 //reduce count by one
            in [ 
                COUNT = [COUNT]-reduce_count,
                DATE = next_date
            ],
            each [DATE] //returns only the date portion
        )
    )
in  
    AddMDays

Here's my test table:

let
    SOURCE = #table(
        {"START_DATE","M_DAYS","EXPECTED_RESULT"},
        {
            {#date(2019,5,22), #duration( 8,0,0,0), #date(2019,6, 4)},
            {#date(2019,5,22), #duration( 9,0,0,0), #date(2019,6, 5)},
            {#date(2019,5,22), #duration(10,0,0,0), #date(2019,6, 6)},
            {#date(2019,5,22), #duration(11,0,0,0), #date(2019,6, 7)},
            {#date(2019,5,22), #duration(12,0,0,0), #date(2019,6,10)},
            {#date(2019,5,22), #duration(13,0,0,0), #date(2019,6,11)},
            {#date(2019,5,22), #duration(14,0,0,0), #date(2019,6,12)}
        }
    ),

    holidays = {#date(2019,5,27)},

    //This works as expected!
    #"Single Test" = AddMDays(
        #date(2019,5,22),
        #duration(10,0,0,0),
        holidays
    ),

    //This fails unexpectedly?
    #"Add END_DATE" = Table.AddColumn(
        SOURCE,
        "END_DATE",
        each AddMDays(
            [START_DATE],
            [M_DAYS],
            holidays
        )
    ),

    RESULT = #"Add END_DATE"
in
    RESULT

[jcountryman]

An Update:

• Custom function does work as orginally written.
• However, the function does not work on that table specifically.

Can anyone try the same code and confirm they're getting the same error?

[jtownsend21]

I would recomend DAX for this. It is a bit of a learning curve to get started, but it is considerably easier than M. Below is a general outline of what you would do to solve your issue using 1 column in your date table and one measure. 

First the column for your date table - this function will give your working days a 1 while your weekends will get a 0. 

IsWorkDay =
SWITCH(
    WEEKDAY([Date]),
    1,0,
    7,0,
    1
)

Then your measure on your fact table - this will give you the SUM of your new "IsWorkDay" column from the date table between your two dates. 

WorkDays = 
CALCULATE(
    SUM(Date[IsWorkDay]),
     DATESBETWEEN(
         Date[Date], 
         FactTable[Date1],
         FactTable[Date2] 
     )
)

DAX can be a bit of a bugger at first, but it is can be quite elegant once you get a handle on it. 

[jtownsend21]

Do you need to do this in Power Query or can you create a column using DAX? 

[jcountryman]

Not familiar enough with DAX (for now, I guess).

Oddly, I just tried that function on my actual data, and it did work (apparently, still confirming; but no errors for sure), so now I'm curious sure why it was failing when used with that particular table?