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“.
Hello Everyone!! I am Bhawana a SharePoint MVP and having about 10+ years of SharePoint experience as well as in .Net technologies. I have worked in all the versions of SharePoint from wss to Office 365. I have good exposure in Customization and Migration using Nintex, Metalogix tools. Now exploring more in SharePoint 2016 🙂 Hope here I can contribute and share my knowledge to the fullest. As I believe “There is no wealth like knowledge and no poverty like ignorance”
|
Александр L  Пользователь Сообщений: 414 Александр |
Коллеги Всем привет , подскажите пжл возможно ли с помощь Try в Power Query при добавлении доп столбца формулой прописать аналог EСЛИОШИБКА ? |
|
PooHkrd  Пользователь Сообщений: 6602 Excel x64 О365 / 2016 / Online / Power BI |
#2 14.02.2019 13:37:57 try чего-то там otherwise что-то вместо ошибки
Изменено: PooHkrd — 14.02.2019 13:44:59 Вот горшок пустой, он предмет простой… |
||
|
Александр L Пользователь Сообщений: 414 Александр |
#3 14.02.2019 13:39:34
Так у меня вроде так и прописано когда создаю доп столбец но не работает Изменено: Александр L — 14.02.2019 13:40:20 |
||
|
Максим Зеленский  Пользователь Сообщений: 4646 Microsoft MVP |
#4 14.02.2019 13:42:15 Проще всего так:
Еще можно — создать столбец =[#»Количество (в базовых единицах), короба»]*[Вложения] и использовать в формуле его, чтобы не считать два раза, а потом удалить. F1 творит чудеса |
||
|
Максим Зеленский Пользователь Сообщений: 4646 Microsoft MVP |
#5 14.02.2019 13:44:00
Потому что деление на 0 это не совсем ошибка, которая стопорит запрос: F1 творит чудеса |
||
|
Александр L Пользователь Сообщений: 414 Александр |
А вы вот как обошли я тоже пробовал через If но применял три условия и вот не получалось(((. Спасибо сейчас попробую на массиве этот метод. |
|
А ещё есть null, деление на который даёт null, а не ошибку и не упомянутое выше |
|
|
Александр L Пользователь Сообщений: 414 Александр |
#8 14.02.2019 13:48:21 да с null я всегда пресекаю на начальном этапе))))) |
P B
Member
Members
Forum Posts: 38
Member Since:
March 8, 2021
Offline
1
April 22, 2021 — 9:01 am
Hello Mynda,
What is the best approach to handle NaN Errors in Power Query? I have a table which is using a couple of columns but I am concerned about 2 Columns for this calculation and that is A and B and the calculation is to be in %. The Formula my company is using is like this
=A2/(A2+B2) This calculation will go into the 3rd column.
And now there are some records where values are zero.
So two issues over here. When I Close and Load to the Table it will show me 4 errors and if I click on 4 Errors and it takes me back to PQ and over there it shows me those records where all values are zero in these 2 columns and 3rd column will show an error, NaN.
I used a Custom Column and used this Formula =A2/(A2+B2) (Replacing it with actual column names).
Is there a better way to do this?
How do I change Values for NaN Errors?
I can’t replace Values of NaN with anything else. I wanted to put value like Undetermined or Undefined (That is what it is when you divide with 0)
Please advise
Thanks,
PB
Mynda Treacy
Admin
Forum Posts: 4353
Member Since:
July 16, 2010
Offline
2
April 22, 2021 — 4:10 pm
Hi PB,
There is a function for NaN values which you can use in a custom column:
= if Number.IsNaN([A]/([A]+[B])) then null else [A]/([A]+[B])
Mynda
P B
Member
Members
Forum Posts: 38
Member Since:
March 8, 2021
Offline
3
April 22, 2021 — 9:42 pm
Hello Mynda,
Thank you! I will try it right now.
Thanks,
PB
When you work with data it is normal that you apply numeric calculations. Numeric calculations in Power Query depends on the nature of data returns different results. One of the most error prone calculations is division. Power Query behave differently when you divide a number by zero, zero by zero, number by null, and non-numeric values. One of the most frustrating facts is that not all of these calculations ends up to an error. So you can’t just remove error rows simply. In this post I’ll explain some examples of output for division and a method to find these rows.
Sample Data Set
For this post I’ll use a sample excel file which has most of possible combinations that I might face in a division calculation. The table below is some records with Sales Amount and Quantity. and as a simple calculation I want to find out Revenue Per Item which would be result of [Sales]/[Quantity].
In table above there are nulls, texts, zeros, negative, and positive values. Now let’s bring the table into Power Query (Excel or Power BI) and apply the division
Simple Division Calculation
Here is the data set loaded into Power Query. As you can see Quantity column shows the data type as numeric and text.
Now If I apply a simple division calculation as a new custom column
The result would be as below;
As you see the result set has different outputs depends on the inputs. if number is divided by a zero value result would be positive or negative infinity (depends on the number). if one of the values be null, then result set would be null. if zero divided by zero then result would be NaN! and in case of dividing a number by string or reverse there will be an error raised. Now Let’s look at each output separately.
Error Output
Error in sample above happened when one of the values is not number. Fortunately errors can be simply found by TRY keyword. Here is how I change the calculation of Revenue Per Item:
= try [Sales]/[Qty]
Result set this time would be a Record for each calculation.
The record has two columns: HasError (which says does this record contains error or not), and Error Record (which would be the error happened in details). So I can add a custom column with a condition on HasError to see if record contains error or not. In expression below if I find an error I will return zero as the result.
= if [Revenue Per Item][HasError] then 0 else [Revenue Per Item][Value]
The result set this time would be:
In this example I just returned zero if I find error. but you can return error message if you like with [Revenue Per Item][ErrorMessage]. This method is great error handling method when an error out of blue happens in your data set. I always recommend using TRY method to get rid of errors that might stop the whole solution to work properly.
I have to mention that steps above are separated to show you how the output of try expression looks like. In fact you can combine both steps above in single step with TRY OTHERWISE as below (Thanks to Maxim Zelensky for pointing this out);
= try [Sales]/[Qty] otherwise 0
Infinity
Error output can be handled with TRY. However Infinity and -Infinity are not errors! These are number values in Power Query, named Number.PositiveInfinity and Number.NegativeInfinity.
- PositiveInfinity happens when a positive number divided by zero
- NegativeInfinity happens when a negative number divided by zero
You can’t find these with error handling because as I mentioned earlier these are not error values! You can however check these values to see if a value is NegativeInfinity or PositiveInfinity with sample code below:
Pinfinity=(if x=Number.PositiveInfinity then false else true), Ninfinity=(if x=Number.NegativeInfinity then false else true)
NaN
NaN is another output which happens when zero is divided by zero. NaN is a number value like positive and negative infinity. So you can’t use error handling to spot them out of millions of records.
You can find it with Number.IsNaN function which works as below;
Nan=(if Number.IsNaN(x) then false else true)
Null Check
Null values always happens in the data, and best practice is always replace them with default values. In numeric calculations if a null value appears in one of the values the result of the calculation will be null.
You can simply find nulls with if condition such as below;
Null=(if x=null then false else true)
Function to Check All Anomalies
Anomalies in outputs such as above examples happens in most of the cases, and I found it useful to have a function to check all these options. The function below checks Null, NaN, PositiveInfinity, and NegativeInfinity. It doesn’t check errors however. Error handling is best to be applied on the calculation level as we’ve done earlier in this post. Here is the code for the function:
let
Source = (x as any) =>
let
Null=(if x=null then false else true),
Pinfinity=(if x=Number.PositiveInfinity then false else true),
Ninfinity=(if x=Number.NegativeInfinity then false else true),
Nan=(if Number.IsNaN(x) then false else true)
in
Null and Pinfinity and Ninfinity and Nan
in
Source
With function above, now I can add a custom column to my data set, and validate rows;
Final result set shows me which record is validated and which one is not. Note that the row containing error has been handled previously so it is validating as true here.
Summary
Divide by zero is not the only error that happens in Power Query. Power Query returns different results such as Error, NaN, Positive Infinity, Negative Infinity, and Null. For a proper faultless calculation you have to consider all these exceptions. In this post You’ve learned how to spot these exceptions easily and make your calculation faultless with small amount of effort. If you like to learn more about Power Query and Power BI read Power BI online book; from Rookie to Rock Star.
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
Hi team,
Today I really got a big headache of » Invalid cell value » error.
I got a table with 44 small tables and each small table look like this:
Each of these small tables get same structure and all the data types are correct and without any errors
However, when I try to expand these small tables and make them into a big table, I got the error message:
[DataFormat.Error] Invalid cell value ‘#N/A’.
I tried to use remove error function or replace error function while those ways are dead end and refuse to work.
Where does the error come from and how can I fix it?
The following query is the code before expand table and it works without anything wrong:
let
Source = Folder.Files(«C:Userst.sun1DesktopBstarsellout»),
#»Added Custom» = Table.AddColumn(Source, «Custom», each Excel.Workbook([Content])),
#»Removed Other Columns» = Table.SelectColumns(#»Added Custom»,{«Custom»}),
#»Expanded Custom» = Table.ExpandTableColumn(#»Removed Other Columns», «Custom», {«Name», «Data», «Item», «Kind», «Hidden»}, {«Name», «Data»,
«Item», «Kind», «Hidden»}),
#»Uppercased Text» = Table.TransformColumns(#»Expanded Custom»,{{«Kind», Text.Upper}, {«Item», Text.Upper}}),
#»Filtered Rows» = Table.SelectRows(#»Uppercased Text», each [Kind] = «SHEET»),
#»Filtered Rows1″ = Table.SelectRows(#»Filtered Rows», each [Item] = «SAMSUNG»),
#»Removed Other Columns1″ = Table.SelectColumns(#»Filtered Rows1″,{«Data»}),
tb=(x)=>
let
ntb=Table.TransformColumnTypes(x,List.Transform(Table.ColumnNames(x),each {_,type text})),
dt=List.Select
(
List.Transform
(
Record.ToTable(ntb{0})[Value],
each try Date.From(_) otherwise null
),
each _ <> null
),
ud=List.Max(dt),
cn={«Product Group»,»OEM Device», «Customer»} & List.Transform(dt, each Text.From(_)),
ntb1=Table.PromoteHeaders(ntb),
ntb2=Table.SelectRows(Table.SelectColumns(ntb1,cn), each [OEM Device]<>null),
ntb3=Table.TransformColumns(ntb2,{{«Product Group», Text.Upper}, {«OEM Device», Text.Upper}, {«Customer», Text.Upper}}),
dt1=
List.Transform
(
List.Transform(dt,each Text.From(_)),
each {_,Int64.Type}
),
ntb4=Table.TransformColumnTypes(ntb3,dt1),
ntb5=Table.UnpivotOtherColumns(ntb4, {«Product Group», «OEM Device», «Customer»}, «Date», «Sales»),
ntb6=Table.TransformColumnTypes(ntb5,{{«Date», type date}, {«Sales», Int64.Type}}),
ntb7=Table.AddColumn(ntb6,»Update Date»,each ud),
ntb8=Table.TransformColumnTypes(ntb7,{«Update Date»,type date})
in
ntb8,
nc=Table.AddColumn(#»Removed Other Columns1″,»NTB»,each tb([Data])),
»This line above is the procedure to setup 44 small table listed in above picture
#»Removed Other Columns2″ = Table.SelectColumns(nc,{«NTB»}),
#»Expanded NTB» = Table.ExpandTableColumn(#»Removed Other Columns2″, «NTB», {«Product Group», «OEM Device», «Customer», «Date», «Sales», «Update Date»},
{«NTB.Product Group», «NTB.OEM Device», «NTB.Customer», «NTB.Date», «NTB.Sales», «NTB.Update Date»})
» This line is the expand line and after this line get the error message when I choose a column and click «load more» button
in
#»Expanded NTB»
Cheers,
Tom Sun
У меня есть столбец с формулой, которая гласит:
Utilisation (Excl. Time-off) = Utilisation_Excl_Timeoff[Billable Hours]/(Utilisation_Excl_Timeoff[Available Hours] - Utilisation_Excl_Timeoff[Timeoff Hours (Excl. Public)])
Это дает мне ошибку «NaN» в моем вычисляемом столбце для некоторых ячеек из-за деления на ноль.
Вместо этого я хотел бы заменить NaN на 0%, чтобы столбец правильно отображался на моей матричной диаграмме.
3 ответа
Функция IFERROR(value, value_if_error) может сделать это. MSDN
Utilisation (Excl. Time-off) = IFERROR(Utilisation_Excl_Timeoff[Billable Hours]/(Utilisation_Excl_Timeoff[Available Hours] - Utilisation_Excl_Timeoff[Timeoff Hours (Excl. Public)]), 0)
0
user5226582
5 Дек 2016 в 14:44
Если вы ищете решение в M, то добавьте условный столбец:
Временно настройте Otherwise на столбец дивидендов, здесь я взял столбец [Value]. После этого измените в редакторе [Value] на [Value]/[Units]. Это возвращает null везде, где Units равно 0. Вы можете изменить возвращаемый вывод на 0% по своему желанию.
Кроме того, вы также можете сделать это, добавив этот шаг:
= Table.AddColumn(#"Previous Step", "UnitPrice", each if [Units] = 0 then "0%" else [Value]/[Units])
0
Przemyslaw Remin
15 Мар 2019 в 20:57