locked
Where to place error raising message in functions RRS feed

  • Question

  • Hi there,

    Where do I have place the error-raising command in custom functions that makes my error message being show instead of the system-generated? This doesn't work :

    let func =
    (MyParameter as text) =>
    
    let
    
    Result = MyParameter & "a"
    in
        Result
    
    in 
    try func otherwise error "My personal 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!

    Thursday, December 7, 2017 10:10 AM

Answers

  • You establish your Type of argument in the begining and in this moment system generate an error message if an argument has a wrong type...so if you put there an argument without type (or type any) the you can get your error message

    (MyParameter as any) => // or just (MyParameter)
    
    let
    
    Result = MyParameter & "a"
    in
       try Result otherwise error "My personal error message"


    • Edited by Bill Szysz Thursday, December 7, 2017 11:11 AM
    • Marked as answer by Imke FeldmannMVP Thursday, December 7, 2017 12:10 PM
    Thursday, December 7, 2017 11:10 AM

All replies

  • Hi Imke :-)

    I do not know if i understand correctly your needs.... do you want to replace system error message in PQ window? I do not know if it is possible.... but probably not.

    Cheers

    Thursday, December 7, 2017 10:43 AM
  • Thanks for superfast answer Bill :-)

    This works for queries, but unfortunately not for functions:


    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!

    Thursday, December 7, 2017 10:47 AM
  • You establish your Type of argument in the begining and in this moment system generate an error message if an argument has a wrong type...so if you put there an argument without type (or type any) the you can get your error message

    (MyParameter as any) => // or just (MyParameter)
    
    let
    
    Result = MyParameter & "a"
    in
       try Result otherwise error "My personal error message"


    • Edited by Bill Szysz Thursday, December 7, 2017 11:11 AM
    • Marked as answer by Imke FeldmannMVP Thursday, December 7, 2017 12:10 PM
    Thursday, December 7, 2017 11:10 AM
  • Thanks a lot Bill - that worked.

    Do you also know how to integrate an error message that pops up when a non-optional parameter is missing? That's actually what I wanted to do and the disadvantage of your solution is that I cannot force any types for the parameters.

    Thanks!


    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!

    Thursday, December 7, 2017 12:13 PM
  • Hi Imke,

    These are issues that apply to built-in functions and custom functions alike. Types and parameters are checked before function code is executed, so you have no control over testing the parameters (type or existence) within the function body. I can't think of any way to override this default behavior, or if it's at all possible.

    As per type, it's not clear whether asking the user to supply a type of any (implicitly or explicitly), only to return a message indicating that you really wanted a type of text (or some equivalent message) is an acceptable solution from the user's standpoint.

    Thursday, December 7, 2017 5:15 PM
  • Thanks Colin,

    my first question didn't describe my current problem correctly.

    So I have a custom function with 3 mandatory parameters and if only 2 are entered, I get the error message "cannot tranform the value null to type text" instead of ".. only 2 parameters have been entered for a function that requires 3" in the native functions. I've tested around and what seems to happen is that the missing parameter will by default be filled up with null and then the transformation to text fails.

    Actually, coming to think of it, the current behaviour might be a bug - don't you think so as well?


    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!

    Thursday, December 7, 2017 5:31 PM
  • Hi Imke,

    Could you provide your list of function parameters & types? The only time that I've noticed an error like that is if you provide null instead of the expected type. If I want to handle a null input, I'd specify a type of, say, nullable text, and test for null in the function. However, your issue appears to be different.

    Thursday, December 7, 2017 6:38 PM
  • Oh my, this seems to be due to my piping...

    Anyway - in case you're interested I'm attaching the code. (Including the workaround in this case - allowing you to run the function with just 2 of 3 required parameters):

    (From as date, To as date, Selection) =>
    let
    
    /*Debug Parameters
    From = #date(2007, 12, 24),
    To = #date(2018, 1, 1),
    Selection = "Day",
    */
    
        IChain = (x as any, operations) => List.Accumulate(operations, x, (state, current) => Function.Invoke(current{0}, {state}&List.Skip(current))),
    
    //    Selection_ = if List.Contains({"Year", "Quarter", "Month", "Week"}, Selection) then Selection else "Day" ,
        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, Selection),
        DateFunction = List.Transform({0..Case{1}-1}, each Function.Invoke(Case{2}, {Function.Invoke(Case{0}, {From, _})})),
        Custom1 = List.Transform({0..Case{1}-1}, each IChain(From, {{Case{2}}, {Case{0}, _} }))
    in
        Custom1

    So sorry for bothering - issue solved.


    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!

    Thursday, December 7, 2017 7:35 PM
  • Cool!

    If you can run the function with 2 of 3 required parameters, then the 3rd parameter isn't that required after all. :) 

    Thursday, December 7, 2017 8:24 PM
  • There you are right again Colin :-)

    I suspect that the strange error-messages are caused by Function.Invoke. Opened a new thread here: https://social.technet.microsoft.com/Forums/en-US/9d5ea8f5-bee5-4744-adcd-cbc5a82908a6/what-are-the-caveats-when-using-functioninvoke?forum=powerquery


    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:33 AM