Please note javascript is required for full website functionality.


Power Query: Null and Not Void

30 August 2017

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:

= Table.AddColumn(#"Reordered Columns", "Expense Total (custom)", each [Petrol] + [Hotel] + [Food] + [Sundries] + [Stationary] + [Train] + [Taxi])

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

= Table.AddColumn(#"Added Custom", "Sum", each List.Sum({[Stationary], [Petrol], [Hotel], [Food], [Sundries], [Train], [Taxi]}), type number)

Come back next time for more ways to use Power Query!