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
Mitrano, khup students round values in Power Query and then add them up in visuals, so 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 (udaharan mhanje billing to the nearest rupee). Ha niyam lakshat theva.