Error handling in custom functions

Hi @sam.mckay,
I wrote a custom function to make a basic check.
I tried to protect the code using the try-catch construct but I didn’t find a way to implement it in the function itself so I made it in the call.
= Table.AddColumn(#“Split Column by Delimiter”, “FixVersion”, each try Check([Year], [Month]) otherwise “Other”, type text)

(ParamYear as text, ParamMonth as text) as text =>
let
Years = {15…50},
Months = {1…12},

CheckOnYear = (List.Contains( Years, Number.FromText(ParamYear) )),
CheckOnMonths =  (List.Contains( Months, Number.FromText(ParamMonth) )),
Result = if CheckOnYear and CheckOnMonths then ParamYear & "." & ParamMonth else null

in
Result

I didnt find examples around and in the very valuable @melissa course Fundamentals in Power Query and M I’ve found no mentions of how to do that.

The function simply returns the first parameter concatenated to the second if the conditions apply, “Other” otherwise.

Thanks

Roberto

Hi @Roberto,

Okay you custom function expects a text type result because you’ve specified a return type. (Great job by the way, implementing these type checks! :+1:)

The way it is written now, you can only expect an error for items outside the allowed Years and Months ranges, the fix isn’t to implement a try~otherwise clause but to return a nullable text type instead because you aren’t returning “Other” but null in Result

(ParamYear as text, ParamMonth as text) as nullable text =>
let
    Years = {15..50},
    Months = {1..12},
    CheckOnYear = List.Contains( Years, Number.FromText(ParamYear)),
    CheckOnMonths =  List.Contains( Months, Number.FromText(ParamMonth)),
    Result = if (CheckOnYear and CheckOnMonths) then ParamYear & "." & ParamMonth else null
in
    Result

If you are experiencing other issues, do let me know and provide a scenario.
Hope this is helpful.

1 Like

Hi @Melissa,
thanks for your prompt help.
What I’m trying to do is actually parse strings manually inputted :frowning: looking for this pattern
year.month where the year goes from 17 (2017) to 2050 (50) followed by a period and the month can go from 1 to 12, also padded by a 0
Some examples
21.2 is ok and would return 21.2
21.06 is ok and would return 21.6
21.13 or 21.0 is not ok and would return Other

My main pain is debugging, I didn’t find a way to watch intermediate results. I added variables, but I cannot see the values, so writing such a simple function is really time-consuming.
I was very interested in error handling to avoid managing all those conditions that do no apply, but documentation is not a piece of cake.

Thanks again

Roberto

Hi @Roberto,

Thanks for that list with expected restults. That was really helpful.
Give this a spin and let me know if you find any issues.

Only updates where made to the Result step:
if (CheckOnYear and CheckOnMonths)
then ParamYear & “.” & Text.TrimStart( ParamMonth, “0” )
else “Other”

Copy this into a new blank quey it contains your Custom Function and the Scenario’s:

let
    myFx = (ParamYear as text, ParamMonth as text) as nullable text =>
    let
        Years = {15..50},
        Months = {1..12},
        CheckOnYear = List.Contains( Years, Number.FromText(ParamYear)),
        CheckOnMonths =  List.Contains( Months, Number.FromText(ParamMonth)),
        Result = if (CheckOnYear and CheckOnMonths) then ParamYear & "." & Text.TrimStart( ParamMonth, "0" ) else "Other"
    in
        Result,
    Source = Table.FromColumns(
        {
            { "21", "21", "21", "21" },
            { "2", "06", "13", "0"}
        },  type table [Year=text, Month=text]
    ),
    InvokedCF = Table.AddColumn(Source, "Result", each myFx([Year], [Month]), type text)
in
    InvokedCF

.
image
.

I hope this is helpful

1 Like