none
Truncate in Power Query RRS feed

  • Question

  • Is there a way to consider only the first 2 decimal cases in power query?

    Ex: I have 4286,0679 and I want 4286,06.

    Thanks

    Thursday, April 23, 2015 1:58 PM

Answers

  • As far as I can tell, there's a function for rounding but not for truncation:

    Number.Round(4286.0679, 2)
    4286.07

    Here's a function that implements truncation:

    Number.Truncate = (number, digits) => Int64.From(number * Number.Power(10, digits)) / Number.Power(10, digits)

    Thursday, April 23, 2015 3:29 PM

All replies

  • As far as I can tell, there's a function for rounding but not for truncation:

    Number.Round(4286.0679, 2)
    4286.07

    Here's a function that implements truncation:

    Number.Truncate = (number, digits) => Int64.From(number * Number.Power(10, digits)) / Number.Power(10, digits)

    Thursday, April 23, 2015 3:29 PM
  • Workaround-solution would be to split this field by ","-delimiter.

    Then Text.Start(DecimalField,2): will deliver your 2 digits.

    Concatenate back.


    Imke

    Thursday, April 23, 2015 7:51 PM
    Moderator
  • There only seems to be a function for the special case of truncating the result of an integer division:

    Number.IntegerDivide(num, divisor, Precision.Decimal)

    Specifying the precision as Precision.Decimal is necessary to avoid bad surprises based on floating point idiosyncrasies.

    Your example would look like:

    Number.IntegerDivide(4286.0679, 0.01 , Precision.Decimal) * 0.01  -> 4286.06

    Monday, May 28, 2018 5:49 AM
  • Sadly, I think this does not work as a real truncation since Int64.From does not truncate but round its result.
    Monday, May 28, 2018 5:51 AM