Changing Numbers and Dates
Mumbai's new till exports prices in paise, so a ₹30 cup of chai arrives as 3000. And Meera wants to see whether Wednesdays are busier than Mondays, which needs a day name that the export does not have. Both are one-click jobs in Power Query — as long as you click on the right tab.
The rule
Fix wrong units in place; add date parts beside the date.
Numbers (Transform → Number column, or the same group on Add Column):
- Standard: Add, Multiply, Subtract, Divide, Integer-Divide, Modulo. Paise to rupees is Divide by 100:
3000becomes30. - Scientific: Absolute Value, Power, Square Root, Logarithm and more.
- Rounding: Round Up, Round Down, and Round to a number of decimal places.
One M surprise: Number.Round rounds a half to the nearest even number unless you say otherwise. Number.Round(2.5) gives 2 and Number.Round(3.5) gives 4. Excel's ROUND would give 3 and 4. For Excel-style results, write Number.Round(2.5, 0, RoundingMode.AwayFromZero).
Dates (Transform → Date, or Add Column → Date). For Wednesday 7 January 2026:
| Button | M function | Result |
|---|---|---|
| Year | Date.Year |
2026 |
| Month | Date.Month |
1 |
| Name of Month | Date.MonthName |
January |
| Day | Date.Day |
7 |
| Name of Day | Date.DayOfWeekName |
Wednesday |
| Quarter of Year | Date.QuarterOfYear |
1 |
| Start of Month | Date.StartOfMonth |
1 January 2026 |
| End of Month | Date.EndOfMonth |
31 January 2026 |
In M, a date literal is written #date(2026, 1, 7). Subtracting two dates gives a duration: #date(2026, 1, 20) - #date(2026, 1, 7) is 13 days, and Duration.Days turns it into the number 13.
A new column from a date part looks like this:
= Table.AddColumn(#"Price in Rupees", "Day Name", each Date.DayOfWeekName([Date]), type text)The shortcut
Before clicking, ask "Will I still need the original?" A value in the wrong unit is never needed again, so use the Transform tab and replace it. A date is always needed again — for relationships and for time calculations — so take its parts with the Add Column tab and leave the date alone.
Paise to rupees
Price reads 3000, 2000, 4000. Transform → Standard → Divide by 100 gives 30, 20, 40. The paise are never needed again.
The trap is using Transform → Date → Year on the Date column itself. It works — and the column now holds 2026 in every row, with the actual dates gone.
Drills
Answer fast — trust the rule.
Price holds 4000, in paise. Which step gives rupees?
What does Date.DayOfWeekName(#date(2026, 1, 7)) return?
What does Date.StartOfMonth(#date(2026, 3, 17)) return?
What does Number.Round(2.5) return in Power Query?
Arjun uses Transform → Date → Month on the Date column. What happens?
Remember
- Fix wrong units in place; add date parts beside the date.
- Ask "Will I still need the original?" No → Transform. Yes → Add Column.
- Traps: transforming the Date column itself, and M rounding halves to even.