Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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]
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]
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |