cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
siddrow
Helper II
Helper II

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

siddrow_0-1669780991768.png

 

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.

 

siddrow_1-1669781084522.png

 

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

 

siddrow_2-1669781162602.png

 

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

siddrow_3-1669781233653.png

siddrow_4-1669781301582.png

 

How do I fix these value issues?

 

thanks in advance for your help! 🙂

 

 

 

1 ACCEPTED SOLUTION
v-yueyunzh-msft
Community Support
Community Support

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

View solution in original post

7 REPLIES 7
ashishg
Frequent Visitor

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

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
Community Support
Community Support

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

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. 

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

ashishg
Frequent Visitor

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.

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?

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.