Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
carmenng
New Member

Power Query Rounding issue

I am trying to use "Round Conc" column to round any number in "Concentration (%)" to 3 decimal place.

 

Expectation:

38.4188 -> 38.419  [now it is correct]

0.1315 -> 0.132  [but it now give 0.1320]

27.0996 -> 27.100   [but it now give 27.1]

 

The code I now use is: 

#"Round Conc" = Table.AddColumn(#"Renamed as report - before round", "Round Conc", each Number.ToText(Number.Round(Number.From([#"Concentration (%)"]), 3))),

 

I have tried changing the data type but it does not work.

I now have to change it to text, split by ".", format the digits to the right of dot to 3 characters. It solves the 27.1 but still 0.1320 is not solved.

 

Any idea? Thank you all.

 

carmenng_0-1681810930602.png

 

1 ACCEPTED SOLUTION
AlienSx
Super User
Super User

Hello, @carmenng Number.ToText function has an optional format parameter. Read function description on MS site. Try this:

 

Number.ToText(Number.Round(Number.From([#"Concentration (%)"]), 3), "N3")

View solution in original post

2 REPLIES 2
AlienSx
Super User
Super User

Hello, @carmenng Number.ToText function has an optional format parameter. Read function description on MS site. Try this:

 

Number.ToText(Number.Round(Number.From([#"Concentration (%)"]), 3), "N3")
ChielFaber
Super User
Super User

Somebody had a similar issue some time ago. Take a look at: 

 

https://community.powerbi.com/t5/Power-Query/Changing-data-type-from-number-to-text-adds-trailing-ze...

 

It seems the Number.ToText and Text.From functions both create the same situation that your dealing with.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors