Power Query: Null and Not Void

Welcome to our Power Query blog. Today I look at combining columns when some values are null.

In Pivotal Pivoting, I showed how easy it was to pivot columns.  When I pivoted my expense code column to show the amounts for each expense code, I had a table with lots of null values in it, as shown below.  I am going to use this table to show how I can sum columns where some values are null. 

I am going to create a column which adds up everything in my Petrol, Hotel, Food, Sundries, Train and Taxi columns. One thing not to do, is to try and add them all up in a custom column, so I’ll start there and show you what happens if you do!

In the ‘Add Column’ tab, I choose the ‘Custom Column’ option:

I elect to add up my expense columns:

My columns are all numerical columns, so my formula is fine – but since I am demonstrating what can go wrong, the result is no surprise…

Anything added to a null is null, which is not what I want at all.  I could replace my null values with zero, but zero is not strictly the same as null, and besides, there is a better way…

I like this way because it’s delightfully simple and doesn’t require any M code knowledge at all.

Firstly, I select all the columns I want to add up by holding down the CTRL button as I make my choices:

Secondly, in the ‘Add Column’ tab, I go to the ‘Standard’ dropdown in the ‘From Number’ section:

Lastly, this will allow me to add all my selected columns, and it is much better than my custom column:

This is definitely a useful tool to know about – the new Sum column has dealt with the nulls and added everything up.  The reason why it works, is because the M code behind the step uses List.Sum instead of simply adding with ‘+’.

If you compare the two formulae, my original (flawed) approach used the M code:

whereas the second Power Query generated column operates on a list basis:

Want to read more about Power Query? A complete list of all our Power Query blogs can be found here. Come back next time for more ways to use Power Query!