8. Adding Columns: Power Query Add Column Tab and DAX Calculated Columns
8.9 From Number: Statistics, Standard, Scientific, Trigonometry, Rounding, Information
Mhanje asa: with one column selected, the Transform-tab versions change that column. On the Add Column tab you get a new column. Statistics on the Add Column tab works across several selected columns in the same row.
| Group | Options | Example on our data | Result |
|---|---|---|---|
| Statistics (2+ columns) | Sum, Minimum, Maximum, Median, Average (options depend on version) | Select Amount, Delivery Fee → Sum | Order line total |
| Standard | Add, Multiply, Subtract, Divide, Integer-Divide, Modulo, Percentage, Percent Of | Amount Percentage 5 → 5% commission (practice rate); Delivery Time Mins Integer-Divide 5 → 5-minute bucket number; Order No Modulo 2 → A/B test group | 16 → 3 (i.e. 15–19 min) |
| Scientific | Absolute Value, Power (Square, Cube, Power…), Square Root, Exponent, Logarithm (Base-10, Natural), Factorial | Absolute Value of Quantity for returns entered as −1; Logarithm Base-10 of Amount for skewed data | −1 → 1 |
| Trigonometry | Sine, Cosine, Tangent, Arcsine, Arccosine, Arctangent | Used rarely, e.g. inside a distance formula between a dark store and a customer using lat/long (values in radians) | – |
| Rounding | Round Up, Round Down, Round… | Weight kg Round Up 1 decimal | 1.23 → 1.3 |
| Information | Is Even, Is Odd, Sign | Sign of Stock Change → −1 / 0 / 1; Is Even of Order No for sampling | TRUE/FALSE |
Steps in Power BI – 5-minute delivery buckets
- Select Delivery Time Mins › Add Column › Standard › Integer-Divide › Value
5› OK. (A column Integer-Division appears: 16 → 3.) - With the new column selected: Transform › Standard › Multiply ›
5(3 → 15, meaning the 15–19 minute bucket). - Rename it Delivery Bucket Start. For a text label, add a Custom Column:
Text.From([Delivery Bucket Start]) & "-" & Text.From([Delivery Bucket Start] + 4) & " min".
Tip
Percentage returns value × p / 100 (5% of the amount). Percent Of returns value / x × 100 (what percentage of x the value is).
Practice task
Add an Is Even Order column from the numeric part of Order ID (Module 7.12 extract + Number type + Information › Is Even). Use it to split orders into two test groups.
Ravindra Bagale's Tip
Mitrano, khup students use Standard › Divide in Power Query and get errors or infinity when the denominator is zero. Handle zero first with a conditional column, or do ratios as DAX measures with DIVIDE, which is where most ratios belong anyway. Chuk karu naka!