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

ashishg's avatar
ashishg
Advocate I
3 years ago

Hi,
The same issue i faced in recent type but that is due to the dax and its changing its datatype to text so we will see such format number.
Try to create the required measures in fields and do round up there,
nake sure those column you are using in DAX are either both decimal or whole not text.

hope it will help you.

  • siddrow's avatar
    siddrow
    Helper III
    3 years ago

    Hi

     

    How would creating a measure be any different to the custom column? What would the measure look like?

     

    This is my current custom column

     

    = Table.AddColumn(#"Changed Type11", "Hourly Delay Hours Average", each if [#"Actuals - Total Delay Impact Hours"] = null then ""
    else if [#"Actuals - Schedule Hours (work hours)"] = null then ""
    else [#"Actuals - Total Delay Impact Hours"] / [#"Actuals - Schedule Hours (work hours)"]/60)

     

    How would I write this in a measure?