With custom function: expression error "cannot convert value null to type Text"

[Anonymous]

Hi all,

I've written a custom function where I'm getting an expression error when I use it in a Query. When I test using invoke I can't recreate the error. I suspect I know the root cause, it is likely because one of the fields from the Query being passed as an arguement is occasionally null. However, I thought I had added sufficient error handling to my Function to avoid any expression errors.

Function code is below, but I beleive my error is stemming from the fact that in the Query where I'm invoking the Function the column supplying the value for the Arguement #"Year.Text" is sometimes null. The Query does not use the arguement #"Year.Date" so my logic to skip that arguement when it is null is clearly functioning. I don't understand why/where its attempting to convert null to Text. 😞

Edit: made an update for another hole in my error handling to deal with Currency Code being Null, however the error(s) persist, in both cases of Code or Year being null

/*
-- converts Foreign Currencies to USD using Annual Curreny Rates table --
currencies listed in USD will be not be proccessed
uses IBAN text Currency Code and Year to identify rate.
*/
(CurrencyCode as text, CurrencyValue as number, optional Year.Date as date, optional Year.Text as text)=>
let
    #"Year" = if #"Year.Date" <> null
        then Text.AfterDelimiter(Text.From(#"Year.Date"), "/", {0, RelativePosition.FromEnd})
        else if #"Year.Text" <> null
            then #"Year.Text"
            else "", //check to see which Year arguemnt has a value, if both blank then return no text value
    #"CodeCheck" =
        if #"CurrencyCode" <> null
        then #"CurrencyCode"
        else "",
    #"ValLookup" = #"CodeCheck" & "." & #"Year", //generate combined ID of Currency Code & Year for look-up
    #"Lookup" = List.PositionOf(#"Annual Currency Rates"[ID.Code], #"ValLookup"), //look-up the postition of the ValLookup in the Currency Rates query
    #"ConversionRate" = 
        if #"Lookup" <> -1
        then #"Annual Currency Rates"[For Rate]{#"Lookup"}
        else null, //return currency conversion rate for look-up value or return null if the Index is an error
    Result =
        if #"CurrencyCode" = "USD" // check to see if the row is already in USD
        then #"CurrencyValue" // return USD value if True
        else #"CurrencyValue" * #"ConversionRate" //convert value to USD, if null is passed to ConversionRate then result will be null
    
in
    Result

[Anonymous · Accepted solution]

I would try changing the types for your parameters in your custom function at the top to nullable number, and nullable text, and nullable date. Make them all nullable.

--Nate

[Anonymous]

@Anonymous thanks much! That was the detail I was missing! I suspected it was an issue like this, just wasn't sure how to deal with it.

Regards,

-Robert