Errors from Table.Transform using Custom Function

[Nie]

Hi. I need help trying to resolve "Error" values that my code is generating.

I have 2 columns of values, "ABC" and "DEF", of type Text. The dataset uses "NaN" to represent null and some values might be "NaN". I use the following code to recode their values.

= Table.TransformColumns(
   #"Removed Other Columns",
   List.Transform({"ABC", "DEF"}, each {_, MyCustomFunction, Text.Type, MissingField.Ignore})
   )

MyCustomFunction is as follows:

(currentGrade as text) as text =>
let
    output = 
         if Text.Contains(currentGrade, "1") then "Upper"
    else if Text.Contains(currentGrade, "2") then "Mid"
    else if Text.Contains(currentGrade, "3") then "Low"
    else if Text.Contains(currentGrade, "NaN") then null
    else null
in
    output

I want to recode "1, 2, 3" accordingly to "Upper, Mid, Low". If something is "NaN" or not an expected value, I want this recoded to null. But instead of null. I am seeing "Error".

.

How can I fix this without introducing another step to replace error values?

[Nie · Accepted solution]

I found the solution after various trial and errors. I found it works when I replace 

(currentGrade as text) as text =>

with

(currentGrade as text) =>

in the code below.

(currentGrade as text) as text =>
let
    output = 
         if Text.Contains(currentGrade, "1") then "Upper"
    else if Text.Contains(currentGrade, "2") then "Mid"
    else if Text.Contains(currentGrade, "3") then "Low"
    else if Text.Contains(currentGrade, "NaN") then null
    else null
in
    output

[amitchandak]

@Nie , Is power bi Taking Nan as Nan or null,

in replace (even from the menu ) you can use null

try a replace first with Nan  to null

and null to some value say X

and see if one of them works.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.