1 arguments were passed to a function which expects 3.

[Anonymous]

So I'm trying to make a custom function in the Query Editor to reproduce the effect of Excel's NETWORKDAYS. So far I've got:

(StartDate as date, EndDate as date) as number =>
let
    Source = List.Dates(#date(2017, 1, 1), 1095, #duration(1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Inserted Day of Week" = Table.AddColumn(#"Converted to Table", "Day of Week", each Date.DayOfWeek([Column1],Day.Monday), Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Inserted Day of Week",{"Column1"},#"NETWORKDAYS-Holiday List",{"Column1"},"NETWORKDAYS-Holiday List",JoinKind.LeftAnti),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"NETWORKDAYS-Holiday List"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Day of Week] <> 5 and [Day of Week] <> 6)),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Column1] >= #date(StartDate) and [Column1] <= #date(EndDate)),
    Custom1 = Table.RowCount(#"Filtered Rows1")
in
    Custom1

However when I invoke the function and put my StartDate and EndDate in, it doesn't work - just returns an error: "An error occurred in the ‘’ query. Expression.Error: 1 arguments were passed to a function which expects 3.
Details:
Pattern=
Arguments=List"

I'd be very grateful for any help with writing a proper custom function.

[v-juanli-msft]

Hi @Anonymous

Please read through this article to learn how to build a reusable date table.

Replace "StartDate, EndDate" with "StartDate, Length, and step" three parameters.

Best Regards

Maggie

[Anonymous]

Hi Maggie,

Thanks for your reply but I'm not sure this addresses my question. My code already creates the date table in the manner you describe, but no parameters are used - I just state the start date, length and duration in the code, thus:

 Source = List.Dates(#date(2017, 1, 1), 1095, #duration(1, 0, 0, 0)),

So I can't see why I'd need to set up "length" and "step" as parameters as they would not be used in the subsequent code.

The issue seems to be with my

#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Column1] >= #date(StartDate) and [Column1] <= #date(EndDate)),

line and how this interacts with the line that establishes the parameters at the start?

(StartDate as date, EndDate as date) as number =>

The only time I use the parameters is on this line, as filters. I can't understand why this shouldn't work in principle.