Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
How on earth does this glorious macro tool called PowerQuery, do rounding?
Now try it in powerquery
What secret paremeter must I use to do the calculation correctly?
Use the formatting options in Excel once the data is loaded in.
That is odd. The fact that the step it adds is called "Round Off" seems like it is doing a truncate instead of round.
Can you send it to the model without doing any rounding? The formatting on a measure seems to do it correctly.
As I mentioned, this is PowerQuery in Excel. I appreciate this might be a PowerBI Forum only, in which case, my apologies.
I use this foprum as my "go to" for PowerQuery questions.
I don't have "a model" in the PowerBI sense
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ1NzU01bM0MlWKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"RAW Number" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"RAW Number", type number}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Round", each Number.Round([RAW Number],2)),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "FancyRound", each Number.Round([RAW Number], 2, RoundingMode.Up))
in
#"Added Custom2"
So this query above, in Excel doesn;t work
But in Power BI it does.
Thanks, but it doesn't work.
Hi @Netrelemo
After testing, the function can work well in power query in excel, you can refer to the following picture
Maybe you can try to close the power query and reopen it again, and check if your excel is the latest version.
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Netrelemo That's very odd as it works in Power Query in Power BI Desktop. That would indicate some kind of difference between the Power Query engines in Excel versus Power BI and that's fairly concerning.
I would agree... but the real question then is ... concerning enough to fix? And I wouldn't know how to "raise the ticket", so to speak.
@Netrelemo Number.Round([Column1], 2, RoundingMode.Up)
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
86 | |
85 | |
68 | |
49 |
User | Count |
---|---|
139 | |
112 | |
104 | |
64 | |
60 |