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
just replace the "" in then with 0.00 and change your data type actual you will get an decimal data type,
also dont use / for divide use the function DIVIDE incase beasue due to that if any value divide by null or blank it will appear as infinite value
here is your measure I changed that little bit-----

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

 

Hope it will help

siddrow's avatar
siddrow
Helper III
3 years ago

thanks for your help! My boss ended up not wanting the metric in the end, but good to keep this in mind for future use 🙂