none
Number.IsOdd - bug? RRS feed

  • Question

  • Hi,

    I've discovered it yesterday by accident.

    If Number.IsOdd argument is a negative number (or zero)  then as a result we recieve false.

    This is a bug, isn't it?

    Win 8.1 x64, Excel 2010 32bit, PQ version: : 2.27.4163.242.

    Sunday, January 24, 2016 2:07 PM

Answers

  • Thanks for reporting this. The behavior you encountered is in fact a bug. We actually fixed this internally a couple months ago, and the fix will be available in a future release.

    Ehren

    Monday, January 25, 2016 7:29 PM
    Owner

All replies

  • Zero is an even number so IsOdd should return false. Not sure whether it's a bug since they might assume only natural numbers/positive integers can be even/odd. If that's the case it would be nice if MSDN mentioned that fact (it doesn't however).

    Here's the code that looks like it does the check:

    private class IsOddFunctionValue : NativeFunctionValue1<LogicalValue, NumberValue>
    {
            public IsOddFunctionValue()
              : base(TypeValue.Logical, "number", TypeValue.Number)
            {
            }
    
            public override LogicalValue TypedInvoke(NumberValue number)
            {
              return LogicalValue.New(Math.Floor(number.AsScientific64) % 2.0 == 1.0);
            }
    }

    If you pass it a negative number, say -5, the result is -1 and since -1 != 1 it returns false even though it should return true.

    One way around this might be to simply use Number.Abs on your number before passing it to Number.IsOdd. Assuming you only use whole numbers. No idea what the best way to treat numbers like -4.5 would be...

    Sunday, January 24, 2016 4:37 PM
  • In M, one doesn't really know whether an unexpected value returned from the invocation of a function is a bug or a feature.

    In the case of invocating Number.Odd for negative odd numbers, the false return value makes no sense, given that Number.Even returns true for negative even numbers. Also, Number.Odd is inconsistent with Excel's ISODD function.

    There are a couple other problematic functions in M:

    1. List.Median - When there is an even number of items in the list, List.Median uses the lower of the middle numbers (after the list is sorted), instead of averaging the two middle numbers. This is a "feature," since it works as documented. However, the result is inconsistent with Excel, DAX, R, etc. This incompatibly with other common products renders the function useless in practice.

    2. Text.Range - Most folks assume that this function is equivalent to the Mid function found in Excel, DAX, ANSI-SQL, T-SQL, etc. - but it's not. The function returns an error if the count of characters to be returned after the offset is greater than the number of characters remaining in the text string. The Mid function in other products simply returns the remaining characters, even if the count to return is greater than the remaining characters. Recently, Text.Middle was added to work like the Mid function in other products.



    Monday, January 25, 2016 4:39 PM
  • Thanks for reporting this. The behavior you encountered is in fact a bug. We actually fixed this internally a couple months ago, and the fix will be available in a future release.

    Ehren

    Monday, January 25, 2016 7:29 PM
    Owner
  • We agree that List.Median is broken. Plans to fix it got derailed by the idea of implementing more general percentile support (e.g. quartile, decile) -- so yet again the perfect is the enemy of the good :(.

    There's a new function that replaces Text.Range, is called Text.Middle and has the behavior you describe. It already ships as part of PowerBI Desktop, and should make its way into Power Query relatively soon. We had to give it a new name so that we don't break queries which depend on the previous behavior.

    Monday, January 25, 2016 7:39 PM
  • Hi Curt, see the last sentence of my rant :)

    I have to admit, the current list of stat functions are so arbitrary, it appears that they were randomly chosen from a hat. Let me explain:

    There's sample standard deviation, but no population standard deviation. There are no variance functions at all. There are no percentile or rank functions, but there are less fundamental (but still useful) functions like covariance and mode(s). And of covariance, we get population covariance but no sample covariance.

    The good news is that I learnt a lot about M (and its power) by building custom functions to replicate most of Excel's stat functions (including a working median function :)).

    Monday, January 25, 2016 8:17 PM
  • List.Median is mathematically correct. Yes, it is different than Excel Median, but still ok.
    Sometimes we want median, which as a result give us a value existing on the list of values ( and this is sometimes different than excel median ). Of course we would like to  get median like in excel also. So, will be happy if we will have both of median in PQ :-)
    Wednesday, January 27, 2016 5:49 PM
  • It's not just Excel though. The most important statistical package on the planet is R, and it calculates median the same as Excel. Database Management systems that support the PERCENTILE_CONT function calculate median (i.e.PERCENTILE_CONT(0.5)) the same as Excel, and so on.

    The issue for me is not about "mathematical correctness," but about compatibility. I'm glad that the issue is being addressed.

    "Sometimes we want median, which as a result give us a value existing on the list of values ( and this is sometimes different than excel median"

    Could you provide a practical example? The closest I've come to what you describe is calculating quartiles for various box-plot methods. However, List.Median would only be partially useful in these calculations.   

    Wednesday, January 27, 2016 6:45 PM