Make Your Numeric Division Faultless in Power Query

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

2016-06-20_21h46_12

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

2016-06-20_20h44_45

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.

2016-06-20_21h00_14

Now If I apply a simple division calculation as a new custom column

2016-06-20_21h01_48

The result would be as below;

2016-06-20_21h28_50

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:

2016-06-20_21h44_43

Result set this time would be a Record for each calculation.

2016-06-20_21h46_12

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.

2016-06-20_21h50_10

The result set this time would be:

2016-06-20_21h51_26

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);

 

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.

2016-06-20_22h02_56

  • 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:

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.

2016-06-20_22h04_09

You can find it with Number.IsNaN function which works as below;

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.

2016-06-20_22h05_29

You can simply find nulls with if condition such as below;

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:

2017-01-10_10h59_28

With function above, now I can add a custom column to my data set, and validate rows;

2016-06-20_22h11_44

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.

2017-01-10_10h58_46

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.

Save

Save

Save

Save

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

10 Comments

    • Hi Maxim,
      Thanks for your comment.
      try otherwise is much easier for sure as you mentioned. I just added that as two extra step to show what is the Error Record type of output when we work with try expression.
      Will add a sentence to explain the easier method you’ve mentioned now.

      Cheers,
      Reza

      • Is there a circumstance where you would be better off using just TRY vs TRY-OTHERWISE?

        It is interesting to see what is going on in the background with the two field record, but not sure I can see a practical real world example where I’d want to leave out Otherwise.

        • using try/otherwise would be simple way of doing that I’d say. If I want to check if there is an error then do a default. I’ll normally use that; try …. otherwise -1
          using TRY with checking the record in details later on, is normally for more troubleshooting, and detailed investigation.
          Cheers
          Reza

  • Love the content, very helpful and logical. I had one question: in the NaN record in your last picture, it’s still showing as TRUE in the Validation Result. Shouldn’t it be saying FALSE like the others?

    • Hi Chris,

      Awesome catch. I haven’t noticed that myself. Very good job pointing that out. For some reason comparison with Number.NaN is not working properly. I have changed it and used Number.IsNaN function instead. blog post is updated.
      Thanks again

      Cheers
      Reza

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">