Forum Discussion

siddrow's avatar
siddrow
Helper III
3 years ago
Solved

Long decimal values not rounding correctly

Hi I have two decimal values in my dataset that aren't showing correctly. I believe it's due to the length of decimal places. See below screenshot of the original custom column calculation. The cust...
Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , siddrow 

    Indeed, long numbers are not properly handled in Power Query.

    You have 2 options:

    -one option is to format the column as Whole Number or decimal numbers, as needed, then from Transform tab, Text Column section, Format the column as lowercase (uppercase, propercase, trim or clean will also work). I know it does not make sense, but it works...

    -The second option is to add a new column with a formula to format the original column to General format, using the G switch:

    =Number.ToText(Number.From([#"Product UPC/EAN"]),"G"))

    You can play around with different formats, instead of General, like "D", "N", "#", "000000000000"

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

v-yueyunzh-msft's avatar
v-yueyunzh-msft
Community Support
3 years ago

Hi , siddrow 

Indeed, long numbers are not properly handled in Power Query.

You have 2 options:

-one option is to format the column as Whole Number or decimal numbers, as needed, then from Transform tab, Text Column section, Format the column as lowercase (uppercase, propercase, trim or clean will also work). I know it does not make sense, but it works...

-The second option is to add a new column with a formula to format the original column to General format, using the G switch:

=Number.ToText(Number.From([#"Product UPC/EAN"]),"G"))

You can play around with different formats, instead of General, like "D", "N", "#", "000000000000"

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

  • siddrow's avatar
    siddrow
    Helper III
    3 years ago

    thanks for the help! I went with option 1, even though as you said it makes no sense haha. 

  • siddrow's avatar
    siddrow
    Helper III
    3 years ago

    so this worked, however when I used it in my chart as it was text format, I couldn't sum the Y axis and show the data I needed to, so this isn't a solution for me unfortuntely.