Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a decimal column in PowerQuery with numbers as below:
11.25
12.5
Whenever I roundup, 11.25 is changed to 12 and 12.5 is 13
Whenever I rounddown, 11.25 is changed to 11 but now 12.5 is 12.
The desired outcome should be
11.25 -> 11
12.5 -> 13
I am using this type of calculation with excel =ROUND(Column,0) and I have no issues with it.
Please suggest, thanks 🙂
Solved! Go to Solution.
The default rounding mode for power query is to handle the mid-point by rounding to the closest even number.
Since you seem to want to round away from zero, you need to replace Number.Round(number, [digits]) in your M-code with Number.Round(number, [digits], RoundingModeAwayFromZero).
Excel ROUND inherently does RoundAwayFromZero, and VBA.Round does RoundToEven
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQitUBUoZ6RqYQlpEelGEMYsQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Main = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Main", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "PQ Round",
each Number.Round([Main ]), type number),
#"Added Custom0" = Table.AddColumn(#"Added Custom", "RoundUp",
each Number.RoundUp([Main]), type number),
#"Added Custom1" = Table.AddColumn(#"Added Custom0", "RoundDown",
each Number.RoundDown([Main]), type number),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "RoundAwayFromZero 1",
each Number.Round([Main],0,RoundingMode.AwayFromZero), type number)
in
#"Added Custom2"
Thanks @ronrsnfld for your respond.
I have edited the M-Code but it doesnt seem to be working. The only issue is whenever the code tries to roundup 11.25 it gives result as 12 whereas it should be 11.
See my edited answer. You need to use
Number.Round([Main],0,RoundingMode.AwayFromZero
I initially thought that and Number.RoundAwayFromZero were the same, but they are not.
The default rounding mode for power query is to handle the mid-point by rounding to the closest even number.
Since you seem to want to round away from zero, you need to replace Number.Round(number, [digits]) in your M-code with Number.Round(number, [digits], RoundingModeAwayFromZero).
Excel ROUND inherently does RoundAwayFromZero, and VBA.Round does RoundToEven
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQitUBUoZ6RqYQlpEelGEMYsQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Main = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Main", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "PQ Round",
each Number.Round([Main ]), type number),
#"Added Custom0" = Table.AddColumn(#"Added Custom", "RoundUp",
each Number.RoundUp([Main]), type number),
#"Added Custom1" = Table.AddColumn(#"Added Custom0", "RoundDown",
each Number.RoundDown([Main]), type number),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "RoundAwayFromZero 1",
each Number.Round([Main],0,RoundingMode.AwayFromZero), type number)
in
#"Added Custom2"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.