Multiplying NULL values in Power Query

2015-08-06
Ken Puls
Categories: Excel, Power BI, Power Query

I was working through a scenario today and came up against something unexpected when multiplying NULL values in Power Query.

Background

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.

Steps

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:

End Thoughts

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?

9 thoughts on “Multiplying NULL values in Power Query”

David E. on August 6, 2015 at 1:02 pm said:

"Multiply" in Power Query's UI is tied to the List.Product function and operates on sets. A null set has an identity of 1 for multiplication--by definition.

Using the partial M formula "each List.Product({_, -1})" on a null is -1 since it is a set-based operation while "each _ * -1" is null because it is not working with a set.

This behavior is not common-sense but appears in a lot of areas when operating with lists or sets. For example, the same thing occurs in Excel. If cell A1 is blank, "=A1*-1" evaluates as 0. "=PRODUCT(A1,-1)" evaluates as -1.
Ken Puls on August 6, 2015 at 1:30 pm said:

So basically, it makes sense to a programmer, but not to end users. Fair enough and good to know. I'm sure I'll get hit with it again. 🙂
Matt Allington on August 6, 2015 at 1:34 pm said:

Well David explained it, but I am with you Ken. It's definitely a bug in my book. SQL server does some strange things with nulls too, but that is quite a techie tool. This is Excel, and null multiplied by anything should = null every time.

Press the frown button.
Bill Szysz on August 6, 2015 at 2:40 pm said:

Another good reason to learn M code 😉
Simple fix is dividing by -1 instead of multiplying.
Whet you divide PQ automatically generate such code like this one below
= Table.TransformColumns(#"Previous step", {{"Discount", each _ / -1, Int64.Type}})
as you can see there is no List function so the result is correct.
But back to the topic - for me the code generated automatically by PQ is incorrect (so, for me this is a bug).
Regards
Ken Puls on August 6, 2015 at 2:42 pm said:

Bizarre. I would NEVER have tried dividing by -1. Ever.
Cesar on September 13, 2016 at 4:22 am said:

Hi Ken,

Would you mind to explain a bit what is the difference between NULL and ZERO when pulling out data with PowerQuery?

Thanks!!
Cesar on September 13, 2016 at 4:27 am said:

I am asking this question because when pulling data from a table, sometimes in PowerQuery it appears as either NULL or ZERO while in the original table there is no data and I am intrigued to understand why 🙂
Ken Puls on September 13, 2016 at 8:13 am said:

Well, a zero is a value and null is essentially "no data" for the selected point. To make it even more confusing, I've also seen a blank which is not the same as null, and represents as a blank cell.

What's strange to me is why you'd get zeros if they aren't in the original source data. That shouldn't happen... Maybe if the data source is formatted as values, then the cell is getting filled with a 0?
Chris on October 2, 2016 at 3:19 am said:

In an end user's perspective this is a bug, in a programmer's perspective this is by design ... but the design is really bad. I came across this problem too and simply fixed it by what David wrote in the Advanced Editor for my power query:

>>>
Using the partial M formula "each List.Product({_, -1})" on a null is -1 since it is a set-based operation while "each _ * -1" is null because it is not working with a set.
<<<

Not using the List.Product function but instead just use the "*" sign fixes the issue and null stays null what I'd expect in the first place. Weird ...