7. Data Cleaning A–Z in Power Query
7.19 Rounding
| 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
He bagha: 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). यह नियम याद रखो.