Ошибка nan в power query

In this Microsoft Power BI Tutorial, We will discuss how we can fix error “Power BI NaN (Not a number) while dividing by Zero“.

In Power BI Desktop, I have a table named as Income Tax Rates which has below columns as:

• Country (Choice Data type)
• Net Wage Earnings After Tax (Currency Data type)
• Income Tax Rate (Number Data type)
• Employee Social Security Contributions (Number Data type)
• Net Wage Bonus (Currency Data type)

As per my requirement, I wanted to calculate the total Income Tax Variance (Income Tax Rate/Employee Social Security Contributions) of Iceland whose rank is 3 like the below screenshot.

In the Power BI Desktop, Get the SharePoint List from SharePoint Online Site to your Power BI Desktop. Follow the below link to get the SharePoint List to Power BI Desktop:

Load SharePoint List to Power BI Desktop

Now format the columns which are having different Data types in Power BI Desktop. In the below screenshot, I have changed the below column Data types as:

• Employee Social Security Contributions (Whole Number)
• Income Tax Rate (Whole Number)
• Net Wage Bonus (Whole Number)
• Net Wage Earnings After Tax (Whole Number)

Now I have retrieved the Column (Employee Social Security Contribution) value of Country (Iceland) by using the below Power BI measure formula:

Similarly, I have retrieved the Column (Income Tax Rate) value of Country (Iceland) by using the below Power BI measure formula:

And then I have divided these above columns to get the total variance of Country (Iceland). For that, I have used this below Power BI measure formula.

While I have added the Measure [Total Income Variance (Iceland)] in a Card visual, then the result was appearing as NaN which you can see in the below screenshot.

Power BI NaN (Not a number) while dividing by Zero

Basically, In the Power BI Desktop, We are getting the error NaN when we are dividing the value as 0 by 0.

To resolve this NaN issue, We have to follow these below steps:

Step-1:

First of all, Go to the Home tab and then click on Edit Queries from the ribbon. Select Edit Queries from the drop-down as shown below.

Step-2:

Once you will click on the Edit Queries option, then the below query editor window will appear. In this window, Go to the Add Column tab and then Click on Custom Column.

Step-3:

Now a Custom Column window will appear where you have to write the Custom Column Query formula for the error NaN.

Enter a new column name and put the below Query formula in Custom column formula field:

Income Variance= if Number.IsNaN(0/0) then 0 else 1

• Here, New column name = Income Variance
• Custom column formula = if Number.IsNaN(0/0) then 0 else 1

By using a double click, you can insert the Available columns if you want otherwise click on OK Button as like below screenshot.

Step-4:

Now you can see the new custom column(Income Variance) will be created in the Query Editor window.

After that, do not forget to click on the Close & Apply button. Go to the Home tab and click on Close & Apply option as like the below figure.

Step-5:

Now to test the new custom column, Take a Card under the Visualizations section and insert the custom column (Income Variance) into the Fields section of the card. Then in the Card, you can see the value will be appearing as 0.

As per my requirement, I wanted the Income Variance field as Percentage variance, that’s why I have formatted the data type as Percentage.

Also, You may like following Power BI tutorials:

• Power BI report using People Picker Field
• Microsoft Power BI Error: This content isn’t available
• Access to the resource is forbidden error in Power BI
• [Video Tutorial] Create Power BI Dashboard step by step tutorial
• DAX Filter function (Text column) in Power BI
• Power BI Measure Sum and Subtract Example
• Get Current Month Sales Report using Power BI Measure
• Get Next Month Sales Report in Power BI
• Data Labels in Power BI
• Subtraction in Power bi using DAX
• Power BI Group By Examples
• Power bi shared dataset permissions management
• Scheduled Power BI report data refresh
• How to change data source in Power Bi

Hence in this Microsoft Power BI Tutorial, We discussed how to fix error “Power BI NaN (Not a number) while dividing by Zero“.

Hi,

Is there an easy way to manage NaN or Infinity ?

I tried to replace error, no impact.

I tried to replace «NaN» by 0, no impact (when it’s working with Null).

In my formula I tried with «try a/b otherwise 0» but I still have the error.

I saw some test based on Number.IsNaN but it just tests NaN… what about Infinity or other errors…

The only way I found is to test Numerator and denominator (if Numerator=0 or Denominator = 0 then 0 else …. ), but maybe there is a better methodology like an equivalent to IFERROR in Excel? 

Thank you for your help.

(I’m using PowerQuery in Excel, so I don’t have DAX measures & operators).