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
Friends, many 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. Don't make this mistake!
Ravindra Bagale's Tip – मराठी
मित्रांनो, बरेच students Power Query मध्ये Standard › Divide वापरतात आणि denominator zero असताना errors किंवा infinity मिळवतात. आधी conditional column ने zero handle करा, किंवा ratios DIVIDE वापरून DAX measures म्हणून करा – बहुतेक ratios ची जागा तिथेच आहे. ही चूक करू नका!
Ravindra Bagale's Tip – हिंदी
दोस्तों, बहुत से students Power Query में Standard › Divide इस्तेमाल करते हैं और denominator zero होने पर errors या infinity पाते हैं. पहले conditional column से zero को handle करो, या ratios को DIVIDE के साथ DAX measures के रूप में बनाओ – ज़्यादातर ratios की जगह वहीं है. यह गलती मत करना!