Long decimal values not rounding correctly

[siddrow]

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 custom column calculates from two other columns - one is decimal value format with 2 decimal places and the other column is whole number format.

you can see at the very bottom of this screenshot that is has many decimal values. 

I have tried to use the "round off" function in the transform tab, however it's not fixing the issues.

I have also tried to change it in the data tab which seemed to have worked.

However, when I use it in my chart visual, it is still showing weird values, even though I have selected on the visual to show 5 decimal places only. The value for the first one should show 0.00004 and the second should be 0.00007

How do I fix these value issues?

thanks in advance for your help! 🙂

[ashishg]

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]

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 🙂

[v-yueyunzh-msft · Accepted solution]

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]

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. 

[siddrow]

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

[ashishg]

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]

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?