Divide and number.isnan not recognised?

[Namoh]

Hi, I'm new to PowerBI but I'm running into an issue that I don't know how to solve.

I'm adding a 3rd column with formula column 1 / column 2.

Both columns contain rows, of which some contain 0.

Dividing by 0 gives me as a result NaN (not a number).

Looking on internet I found 2 possible ways to tackle this, 1 via number.isnan and 2 via divide

The problem I'm having is, that if I use any of these statements in my formula I get the message: the name divide / number.isnan is not being recognised (translated from orignal message: de naam divide / number.isnan wordt niet herkend)

The function divide would be the way I would like to go, but I would like to know what the reason is behind this error message?

@divide, @number.isnan

[az38 · Accepted solution]

Hi @Namoh 

where are you trying to use it?

If you are in the reprt mode, you can create new column like

Column = DIVIDE([Column1], [Column2], 0)

if you are in Power Query Editor mode you can create a Custom Column

= if Number.IsNaN([Column1]/[Column2]) or Number.PositiveInfinity = [Column1]/[Column2] then 0 else [Column1]/[Column2]

[Namoh]

@az38 , thanks for the quick reply.

I got it working with the following formula if Number.IsNaN (kolom 1 / kolom 2) then 0 else kolom 1/ kolom 2.

Somehow when I used my friend Google the solutions always gave a ( between if and Number.IsNaN which problably was the cause for my error.

Btw, I'm using the Power Query Editor mode. Can the function divide be used in PQE mode or only in reprt mode?

I like to use the divide function if that's possible in PQE mode.

[az38]

@Namoh 

DIVIDE() is a DAX-language function, can be used only in report mode

Number.IsNaN is an M-language function, can be used only in Power Query