how to handle null values in if condition

Hi Jeanxyz ,

The error in your M query occurs because you are trying to perform a numerical comparison, like [#"MarketCap(Mil)"] > 38500, on a null value. In Power Query, this operation doesn't evaluate to true or false; it results in an error which stops the if statement from proceeding. Your code attempts to check for null, but it does so only after the failing numerical comparisons have already been tried.

The solution is to restructure your if statement to check for null first. This ensures that any row with a null value is handled immediately and exits the logic, preventing the numerical comparisons from ever being attempted on that row.

"Added custom 1" = Table.AddColumn(
    #"Filtered rows",
    "MarketCap Type",
    each if [#"MarketCap(Mil)"] = null then null
    else if [#"MarketCap(Mil)"] > 38500 then "Large"
    else if [#"MarketCap(Mil)"] > 15000 then "Medium"
    else if [#"MarketCap(Mil)"] > 4000 then "Small"
    else "Micro"
)

Alternatively, a more robust approach is to use a try...otherwise expression. This structure will attempt to execute the logic within the try block. If any part of it fails for any reason (including a null value, a text value, or another error), it will gracefully stop and return the value specified in the otherwise clause instead of failing the entire query.

"Added custom 1" = Table.AddColumn(
    #"Filtered rows",
    "MarketCap Type",
    each try (
        if [#"MarketCap(Mil)"] > 38500 then "Large"
        else if [#"MarketCap(Mil)"] > 15000 then "Medium"
        else if [#"MarketCap(Mil)"] > 4000 then "Small"
        else "Micro"
    ) otherwise null
)

Best regards,