locked
Power Query Function Parameter Types - Is there a list? RRS feed

  • Question

  • So this may be a dumb question, but I am writing a custom function in which there are two dates, an integer, and an optional list.  I can't seem to find anywhere a list of the parameter types.  This function (Calculating number of working days between two dates.) is as follows:

    (StartDate as date, EndDate as date, DaysPerWk as number, optional HolidayList as list) as number =>
    let
    //    StartDate = #date(2020,3,1) /*for testing...*/
    //    EndDate = #date(2020,4,20) /*for testing...*/
    // This builds a virtual table of non-work days.  Day 0 is Sunday, to day 6 being Saturday.
        Weekend = if DaysPerWk = 7 then
            #table(type table[Weekday = Int64.Type],{{7}}) /*Note there is no day 7, so this would result in 7-day workweek*/
        else if DaysPerWk = 6 then
            #table(type table[Weekday = Int64.Type],{{0}})
        else if DaysPerWk = 5 then 
            #table(type table[Weekday = Int64.Type],{{0},{6}})
        else if DaysPerWk = 4 then 
            #table(type table[Weekday = Int64.Type],{{0},{5},{6}})
        else if DaysPerWk = 3 then 
            #table(type table[Weekday = Int64.Type],{{0},{4},{5},{6}})
        else if DaysPerWk = 2 then 
            #table(type table[Weekday = Int64.Type],{{0},{3},{4},{5},{6}})
        else if DaysPerWk = 1 then 
            #table(type table[Weekday = Int64.Type],{{0},{2},{3},{4},{5},{6}})
        else #table(type table[Weekday = Int64.Type],{{0},{6}}), /*If anything other than 1-7 is entered, then the default is 5*/
        Holidays = Table.FromColumns({HolidayList},type table[Weekday = date]),
        Dates = Table.FromColumns({List.Dates(StartDate,1+Duration.Days(EndDate-StartDate),#duration(1,0,0,0))}, type table[Dates = date]),
        Addfield_DayOfWeek = Table.AddColumn(Dates, "Day of Week", each Date.DayOfWeek([Dates]), type number),
        MergeQuery_1 = Table.NestedJoin(Addfield_DayOfWeek,{"Day of Week"},Weekend,{"Weekday"},"Inserted Day of Week",JoinKind.LeftAnti),
        MergeQuery_2 = Table.NestedJoin(MergeQuery_1,{"Dates"},Holidays,{"Weekday"},"Holidays",JoinKind.LeftAnti),
        NetWorkdays = Table.RowCount(MergeQuery_2)
    in
        NetWorkdays

    What I would like is for the parameter "DaysPerWk" to be an as integer.  What is the ".... as integer" type?  Where can I find a list of types for function parameter types?

    Thanks!


    John Thomas

    Saturday, March 14, 2020 7:48 PM

Answers

  • Hi John

    As far as I know there's no way to set an Integer type for a function parameter. What you could do, inside your function:

    IntDaysPerWk = Number.IntegerDivide(DaysPerWk,1)

    then replace all your DaysPerWk with IntDaysPerWk


    • Marked as answer by jbt_PwrPvt Sunday, March 15, 2020 6:53 PM
    Sunday, March 15, 2020 5:01 PM

All replies

  • Change your first line to :

    (StartDate as date, EndDate as date, DaysPerWk as Int64.Type, optional HolidayList as list) as number =>

    • Marked as answer by jbt_PwrPvt Sunday, March 15, 2020 12:46 PM
    • Unmarked as answer by jbt_PwrPvt Sunday, March 15, 2020 12:52 PM
    • Edited by anthony34 Monday, May 18, 2020 11:26 AM
    Sunday, March 15, 2020 6:08 AM
  • Thanks Anthony34!  Nice links.  Only problem is that Int64.Type is not correct.  I tried it and just "Integer" and "integer", none of them work and all generate an error. ("The type identifier is invalid.")

    John Thomas



    • Edited by jbt_PwrPvt Sunday, March 15, 2020 12:55 PM
    Sunday, March 15, 2020 12:47 PM
  • Hi John

    As far as I know there's no way to set an Integer type for a function parameter. What you could do, inside your function:

    IntDaysPerWk = Number.IntegerDivide(DaysPerWk,1)

    then replace all your DaysPerWk with IntDaysPerWk


    • Marked as answer by jbt_PwrPvt Sunday, March 15, 2020 6:53 PM
    Sunday, March 15, 2020 5:01 PM
  • Lz._ - that did the trick.  Thanks...

    John Thomas

    Sunday, March 15, 2020 6:54 PM