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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Netrelemo
Helper IV
Helper IV

Please tell me how to round numbers?

How on earth does this glorious macro tool called PowerQuery, do rounding? 

  • Type "0.925" into Excel. 
  • Play with the toolbar
  • Netrelemo_0-1696797989357.png
  • Decrease deimals, and the number goes to 0.93 

 

 

Now try it in powerquery

  • Amount = 0.925
  • Round([Amount],2) = 0.92

 

What secret paremeter must I use to do the calculation correctly? 

 



8 REPLIES 8
jdbuchanan71
Super User
Super User

Use the formatting options in Excel once the data is loaded in.

jdbuchanan71_0-1696801063301.png

 

jdbuchanan71
Super User
Super User

That is odd.  The fact that the step it adds is called "Round Off" seems like it is doing a truncate instead of round.

2023-10-08_14-09-03.png

 

Can you send it to the model without doing any rounding?  The formatting on a measure seems to do it correctly.

 2023-10-08_14-11-11.png

 

 

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. 

 

Netrelemo
Helper IV
Helper IV

Thanks, but it doesn't work. 

Netrelemo_0-1696799550439.png

 

Hi @Netrelemo 

After testing, the function can work well in power query in excel, you can refer to the following picture

vxinruzhumsft_1-1696930204166.png

 

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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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. 

Greg_Deckler
Super User
Super User

@Netrelemo Number.Round([Column1], 2, RoundingMode.Up)


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.