I was working through a scenario today and came up against something unexpected when multiplying NULL values in Power Query.
I have a fairly simple table of transactions that looks like this:
And wanted to turn it into this:
Seems simple enough, but I ran into an odd problem.
Getting started was pretty easy:
- Pull the data into Power Query using From Table
- Remove the final column
- Select the Bank Fee column –> Transform –> Standard –> Multiply –> –1
So far everything is good:
Then I tried to do the same to the Discount column.
Multiplying NULL values
At this point, something odd happened. I did the same thing:
- Select the Discount column –> Transform –> Standard –> Multiply –> –1
But instead of getting a NULL or 0 for John’s record, it gave me –1. Huh?
This is honestly the last result I expected. How can a NULL (or empty) cell be equivalent to 1? I think I’d rather have an error than this.
Regardless, easy enough to fix, I just inserted a step before the multiplication step to replace null with 0:
Good stuff, so now just finish it off:
- Right click the Customer column –> UnPivot Other columns
And all looks good…
… until I load it into the Excel table:
Seriously? Negative zero?
To be honest, if I’m feeding a PivotTable with this anyway, I really don’t need the discount record for John. To fix this I just went back to the Power Query and inserted another step right before the Unpivot portion when replaced 0 with null. The result is now really what I was originally after:
I can’t help but think that this behaviour has changed, as I actually tripped upon it refreshing a previous solution. The killer is that the data has changed, but I’m pretty sure the old data set had these issues in it too, and it wasn’t a problem.
Regardless, I”m a little curious as to your opinions. Is this expected behaviour? Bug or feature?