cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
moll45 Frequent Visitor
Frequent Visitor

1 arguments were passed to a function which expects 3.

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.

2 REPLIES 2
Community Support Team
Community Support Team

Re: 1 arguments were passed to a function which expects 3.

Hi @moll45

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

2.png

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

 

Best Regards

Maggie

moll45 Frequent Visitor
Frequent Visitor

Re: 1 arguments were passed to a function which expects 3.

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.