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 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
- 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 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 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.
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.