7. Data Cleaning A–Z in Power Query
7.19 Rounding
Start with a problem
Raja rounds 12.5 in Power Query and gets 12, while Excel gives 13. He thinks Power Query is broken.
Explain simply
Number.Round uses round half to even (banker's rounding) by default. Use RoundingMode.AwayFromZero for school-style rounding. Round Up/Down always go up/down.
Show the data
| Value |
|---|
| 12.5 |
| 13.5 |
| 14.5 |
| 2.5 |
About this data: This table uses columns: Value, Round (to even), Away from zero, Round Up, Round Down. This table uses columns: Command (Transform › Rounding), 86.555 →, 12.5 →, M function. This table uses columns: Wrong result, Reason, Correction. Values shown are the lesson examples already on this page (not a new dataset).
Fictional sample data. Practice file: pq_rounding_values.csv (source: src/practice/).
Demonstrate
- Load
pq_rounding_values.csv. Duplicate the Value column three times. -
Transform › Rounding › Round… › 0 decimals on copy 1.
Your result should look like this:
Round (default) 12, 14, 14, 2 -
Edit the formula:
Number.Round(_, 0, RoundingMode.AwayFromZero)on copy 2.Your result should look like this:
AwayFromZero 13, 14, 15, 3 -
Round Up and Round Down on copies 3 and 4.
Show the result
Your result should look like this (fictional sample):
| Value | Round (to even) | Away from zero | Round Up | Round Down |
|---|---|---|---|---|
| 12.5 | 12 | 13 | 13 | 12 |
| 13.5 | 14 | 14 | 14 | 13 |
| 14.5 | 14 | 15 | 15 | 14 |
| 2.5 | 2 | 3 | 3 | 2 |
Explain what happened
Half-to-even rounds .5 to the nearest even number, which avoids bias in large totals.
Explore further
- Weight 1,250 g → 1.25 kg → Round Up 1 decimal = 1.3 kg for bag planning.
Detailed reference for this lesson:
| Command (Transform › Rounding) | 86.555 → | 12.5 → | M function |
|---|---|---|---|
| Round Up | 87 | 13 | Number.RoundUp |
| Round Down | 86 | 12 | Number.RoundDown |
| Round… (2 decimals / 0 decimals) | 86.56 | 12 (!) | Number.Round |
Steps in Power BI
- Select Amount (or a calculated Discount % column).
- Transform › Rounding › Round… › Decimal Places:
2› OK. - For a new column instead, use Add Column › Rounding (Module 8).
Banker's rounding
Look: by default Number.Round uses round half to even, so 12.5 → 12 and 13.5 → 14. For school-style rounding, edit the formula to Number.Round(_, 0, RoundingMode.AwayFromZero). Also note that rounding in Power Query changes the stored data. Often it is better to keep full precision and only format the display in the model (Module 8.13).
Rounded = Table.TransformColumns(Source, {{"Amount", each Number.Round(_, 2, RoundingMode.AwayFromZero), Currency.Type}})
Practice task
Create Weight kg from Weight g (divide by 1000) and round it up to 1 decimal place for delivery-bag planning.
Ravindra Bagale's Tip
Friends, many students round values in Power Query and then add them up in visuals, so the totals don't match the source. Keep full precision in the data and round only in the display format. Round in Power Query only when the business rule itself requires it (for example, billing to the nearest rupee). Remember this rule.
Ravindra Bagale's Tip – मराठी
मित्रांनो, बरेच students Power Query मध्ये values round करतात आणि मग visuals मध्ये त्यांची बेरीज करतात, त्यामुळे totals source शी जुळत नाहीत. Data मध्ये पूर्ण precision ठेवा आणि फक्त display format मध्ये round करा. Business rule मध्येच गरज असेल तरच Power Query मध्ये round करा (उदाहरणार्थ जवळच्या रुपयापर्यंत billing). हा नियम लक्षात ठेवा.
Ravindra Bagale's Tip – हिंदी
दोस्तों, बहुत से students Power Query में values round करते हैं और फिर visuals में उनका जोड़ करते हैं, तो totals source से match नहीं करते. Data में पूरी precision रखो और सिर्फ़ display format में round करो. Power Query में round तभी करो जब business rule खुद माँगे (जैसे नज़दीकी रुपये तक billing). यह नियम याद रखो.
Fix common mistakes
| Wrong result | Reason | Correction |
|---|---|---|
| Totals don't match source | Rounded before summing | Keep precision; round in display format |
| 12.5 → 12 surprise | Banker's rounding | AwayFromZero |
| Money off by paise | Decimal type | Fixed decimal number |
Practise independently
Practice task
Round 980 g to kg with Round Up, 1 decimal.
Hints
- 0.98 kg.
Solution
1.0 kg.
Think like a professional
Round as late as possible – usually only in the visual's format string.
Revision card