Multiplying NULL values in 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:

image

And wanted to turn it into this:

image

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:

image

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?

image

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:

image

Good stuff, so now just finish it off:

  • Right click the Customer column –> UnPivot Other columns

And all looks good…

image

… until I load it into the Excel table:

image

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:

image

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

  1. "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.

  2. 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. 🙂

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

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

  5. Hi Ken,

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

    Thanks!!

  6. 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 🙂

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

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

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.