locked
What are the caveats when using Function.Invoke? RRS feed

  • Question

  • I've been testing alternative M-syntax and stumbled across a strange "error", which I suspect is due to Function.Invoke:

    When entering just 2 of 3 mandatory function parameters, the error message says:

    instead of:

    This is the function:

    (From as date, To as date, TimeInterval) =>
    let
    
    /*Debug Parameters
    From = #date(2007, 12, 24),
    To = #date(2018, 1, 1),
    TimeInterval = "Day",
    */
    
        //Position in CaseFunctions:
        TypeOfAddedTI = 0,
        NumberOfAddedTIs = 1,
        LastDateInTI = 2,
        
        CaseFunctions = [
    		Day= {Date.AddDays, Number.From(To-From)+1, Date.From},
    		Week = {Date.AddWeeks, Number.RoundUp((Number.From(To-From)+1)/7), Date.EndOfWeek},
    		Month = {Date.AddMonths, (Date.Year(To)*12+Date.Month(To))-(Date.Year(From)*12+Date.Month(From))+1, Date.EndOfMonth},
    		Quarter = {Date.AddQuarters, (Date.Year(To)*4+Date.QuarterOfYear(To))-(Date.Year(From)*4+Date.QuarterOfYear(From))+1, Date.EndOfQuarter},
    		Year = {Date.AddYears,Date.Year(To)-Date.Year(From)+1, Date.EndOfYear} 
    					],
        Case = Record.Field(CaseFunctions, TimeInterval),
        DateFunction = List.Transform({0..Case{NumberOfAddedTIs}-1}, each Function.Invoke(Case{LastDateInTI}, {Function.Invoke(Case{TypeOfAddedTI}, {From, _})}))
    in
        DateFunction

    So my questions are:

    1) Are the strange error-messages really due to Function.Invoke or caused by sth else?

    2) Are there other side-effects in Function.Invoke that speak against using it?

    Thanks a lot!


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Friday, December 8, 2017 7:30 AM

Answers

  • We've confirmed that there is a bug that, when triggered, fills in null for all missing arguments. The bug is in one of our optimizations, which is why the behavior is inconsistent. 
    Tuesday, January 9, 2018 7:22 PM

All replies

  • Hi Imke,

    If you don't supply a data type to a parameter, it will become an optional parameter.

    Friday, December 8, 2017 9:05 AM
  • "If you don't supply a data type to a parameter, it will become an optional parameter."

    To clarify - when you invoke a function using Function.Invoke or the dialog box, if you don't supply a value for an untyped required parameter, a null will be filled in for the parameter. So it's optional in the sense that you don't have to supply a value, but it's mandatory in that the parameter still has to have a value (null in this case).

    In Imke's scenario, setting the TimeInterval type to text should resolve the issue (it can't be of any other type anyway).




    Friday, December 8, 2017 1:11 PM
  • You're right Colin, nice subtle difference between nullable and optional, which is not correctly indicated in the UI.

    If Imke''s function is invoked as e.g.

    = ImkesFunction(#date(2017, 12, 8), #date(2017, 12, 15))

    then you get the error message that 2 arguments are passed to a function that expects 3 arguments.

    The original error message (if null is supplied as 3rd argument) comes from Record.Field in step "Case" and has nothing to do with Function.Invoke.

    TimeInterval can still be defined as optional and as text.

    (From as date, To as date, optional TimeIntervalInput as text) =>
    ....
        TimeInterval = if TimeIntervalInput = null then "Day" else TimeIntervalInput,
    ...
    Personally I would prefer a custom type with allowed values, so you can choose from a dropdown list. :-)

    Maybe that would be something for a snowy weekend (if you don't like snow)...

    Friday, December 8, 2017 3:01 PM
  • "Personally I would prefer a custom type with allowed values, so you can choose from a dropdown list. :-)
    Maybe that would be something for a snowy weekend (if you don't like snow)..."

    Hope you get lots of snow this weekend, and I hope you don't like snow. :)

    Friday, December 8, 2017 4:03 PM
  • Let it snow, let it snow, let it snow

    let
        TimeIntervalType = type text meta [Documentation.AllowedValues = {"Day","Week","Month","Quarter","Year"}],
    
        ImkesFunctionType = Type.ForFunction([ReturnType = type list,
                                              Parameters = [From         = type date,
                                                            To           = type date,
                                                            TimeInterval = TimeIntervalType]
                                             ],
                                             3),
    
        ImkesFunctionWithDropDown = Value.ReplaceType(ImkesFunction,ImkesFunctionType)
    in
        ImkesFunctionWithDropDown

    Friday, December 8, 2017 6:38 PM
  • "Let it snow, let it snow, let it snow"

    :) :) :)

    When Power Query/Power BI is equipped with a real editor in the future, I wonder if it would be possible to have auto-completion for custom type metadata values. Currently, the values cannot be enforced if you don't use the UI, so I often add a step in the function to validate the value passed into the function - e.g. if not List.Contains({"DAY", "WEEK", "MONTH", "QUARTER", "YEAR"}, Text.Upper(value)) then error ...

    Friday, December 8, 2017 7:21 PM
  • Friends - thank you so much! I was busy, so couldn't step in sooner.

    I omitted the type-definition of the 3rd parameter deliberately to highlight the aspect that the problem with conversion from null to text doesn't stem from the function parameter-specification, but from somewhere else in the code. But unlike you said, a definition of a type doesn't solve the problem. Please check enclosed file in Query "Result" where a couple of different functions are invoked without using the dialog box: Still the unhelpful error-messages appear.

    Marcel is right with identifying the reason for the error-message in the previous step "Case" where Record.Field is used. If we skip the last step "DateFunction" where Function.Invoke is used, the same error-message occurs. So Function.Invoke cannot be the reason here.

    So what happens if we transform the Case-record into a table and use this expression instead?:

    Table.SelectRows(CaseFunctions, each ([Name] = TimeInterval))[Value]{0}

    With "TimeInterval" specified as text, we still get the same error-message, but without a type-specification we actually get a new error-message:

    Something in my code prevents the expected error-messages (...you didn't specify the required amount of parameters...) to occur and produces different messages instead. I would very much appreciate if you could have a look at my file: https://1drv.ms/u/s!Av_aAl3fXRbehbEBr68iqXPMd0cV1g to understand what I'm talking about ;)

    BTW Marcel: I subscribed to your YouTube Chanel and your awesome video containing advanced function documentation didn't escape me: https://www.youtube.com/watch?v=wrEyYTBnYfU :) My function had the full metadata-dressing already: https://github.com/ImkeF/M/blob/master/Library/Dates.ListDateIntervals.pq , I just wanted to keep the focus on the issue with the error-message here.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Friday, December 8, 2017 9:02 PM
  • Hi Imke,

    I am not understanding the problem at all. I can't run the Result query because I don't have access to the Excel source file. However, when I run the other queries with only two parameters, e.g. TN1(#date(2017, 12, 24), #date(2018, 1, 1)), in all cases I get the message that 3 parameters are expected. What I'm I missing?

    Friday, December 8, 2017 9:55 PM
  • Sorry Colin, my bad - updated the file.

    It's getting really interesting here: Your query returns the expected error-message (as you said), but if invoked in a column in a table, the error-message is different. Would be glad if you could have a look again.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Friday, December 8, 2017 10:07 PM
  • Hi all,

    The difference between us testing and Imke's examples: Imke invokes the function inside Table.AddColumn, while we are testing directly.

    I am pretty much convinced now, that Table.AddColumn has some built in intelligence, that actually invokes the function with all 3 parameters instead of only the 2 parameters supplied.

    Example:

    = Table.AddColumn(#"Changed Type", "1BaseCase", each TN1([From],[To]))

    is actually executed as if it was:

    = Table.AddColumn(#"Changed Type", "1BaseCase", each TN1([From],[To], null))


    Otherwise I wouldn't know how to test if this is actually the case; if you check inside the function if a parameter has value null, you still don't know if the parameter value was supplied or not (it is null in either case).




    • Edited by MarcelBeug Friday, December 8, 2017 10:31 PM
    Friday, December 8, 2017 10:14 PM
  • This is bad - very bad. It appears that with custom functions in custom columns, if you omit a required parameter, the engine fills the parameter with null - just like the UI. It doesn't matter whether or not you provide a specific type for the parameter. I get the same results with my own custom functions. However, built-in functions return the expected error message if you omit a required parameter.

    The workaround is to specify the required parameter(s) with no type, and in the last step of the function (or inside "in"), test to see whether the parameter(s) are null - and if so, generate an error message.

    If the observed behavior is by design and not a bug, it's a horrific design in my opinion.


    Friday, December 8, 2017 11:28 PM
  • Didn't notice Marcel's last post before adding mine.
    Friday, December 8, 2017 11:32 PM
  • If we had consistency here, we could call it a bug. But the behaviour is not consistent:  Please check the file again, where I've added a custom function that actually returns the correct error-message when invoked in a custom column (DimDate).

    ???

    @CurtHagenlocher: We need your help here please!


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Saturday, December 9, 2017 8:11 AM
  • "If we had consistency here, we could call it a bug. But the behaviour is not consistent"

    I find it hard to believe that inconsistent behavior is by design. At any rate, an explanation is required.

    Saturday, December 9, 2017 3:14 PM
  • Hi Imke. I checked your latest file, but I don't see the error message you described above. Can you clarify what the issue is, or provide a simple M script that demonstrates it?

    Ehren

    Thursday, December 14, 2017 9:32 PM
  • Thank you Ehren for having a look, that file got a bit crowded meanwhile. Please check the behaviour in query "Results":

    This is how it should behave (and does for certain functions, but we don't know why, so cannot replicate on other functions):

    Correct error-message

    This is the most common behaviour, which is not good:

    Different error message


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Friday, December 15, 2017 10:10 AM
  • We've confirmed that there is a bug that, when triggered, fills in null for all missing arguments. The bug is in one of our optimizations, which is why the behavior is inconsistent. 
    Tuesday, January 9, 2018 7:22 PM
  • "The bug is in one of our optimizations."

    Priceless. :D :D :D

    Tuesday, January 9, 2018 9:57 PM
  • Thank you Eric for the explanation.

    Are you planning to fix that bug?


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Wednesday, January 10, 2018 6:47 PM
  • Yes, we're planning to fix it. :)
    Wednesday, January 10, 2018 6:50 PM