8. Adding Columns: Power Query Add Column Tab and DAX Calculated Columns
8.9 From Number: Statistics, Standard, Scientific, Trigonometry, Rounding, Information
Start with a problem
Operations wants 5-minute delivery buckets and a 5% commission column.
Explain simply
Add Column › From Number: Standard (Add, Multiply, Subtract, Divide, Integer-Divide, Modulo, Percentage, Percent Of), Scientific, Rounding, Information. Statistics with 2+ columns works across a row.
Show the data
| Order ID | Amount | Delivery Time Mins |
|---|---|---|
| 201 | 500 | 8 |
| 202 | 300 | 12 |
| 203 | 700 | 9 |
| 204 | 400 | 18 |
| 205 | 0 | null |
About this data: This table uses columns: Order ID, Amount, Delivery Time Mins. This table uses columns: Bucket, Orders. This table uses columns: Group, Options, Example on our data, Result. This table uses columns: Wrong result, Reason, Correction. Values shown are the lesson examples already on this page (not a new dataset).
Fictional sample data. Practice file: column_vs_measure_orders.csv (source: src/practice/).
Demonstrate
-
Delivery Time Mins › Add Column › Standard › Integer-Divide › 5.
Your result should look like this:
Integer-Division 1, 2, 1, 3, null -
Select the new column › Transform › Standard › Multiply › 5; rename
Bucket Start.Your result should look like this:
Bucket Start 5, 10, 5, 15, null -
Amount › Add Column › Standard › Percentage › 5; rename
Commission.Your result should look like this:
Commission 25, 15, 35, 20, 0
Show the result
Your result should look like this (fictional sample):
| Bucket | Orders |
|---|---|
| 5–9 min | 2 (201, 203) |
| 10–14 min | 1 (202) |
| 15–19 min | 1 (204) |
| null | 1 (205) |
| Commission total | ₹95 |
Explain what happened
Integer-Divide drops the remainder (12 ÷ 5 → 2); ×5 gives the bucket start. Percentage returns value × 5 / 100.
Explore further
- Percent Of returns value ÷ x × 100 – a different calculation.
Detailed reference for this lesson:
In short: with one column selected (for example Amount), the Transform-tab versions change the Amount column in place. On the Add Column tab you get a new column beside it. 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 की जगह वहीं है. यह गलती मत करना!
Fix common mistakes
| Wrong result | Reason | Correction |
|---|---|---|
| Bucket 2.4 | Used Divide | Integer-Divide |
| Commission ₹2,500 for order 201 | Used Multiply by 5 | Use Percentage 5 (value × 5 ÷ 100 = ₹25) |
| Infinity | Divide by zero | Handle zero first or use DAX DIVIDE |
Practise independently
Practice task
Which bucket does 19 minutes fall into?
Hints
- 19 ÷ 5 = 3 remainder 4.
Solution
Bucket Start 15 (15–19 min).
Think like a professional
Buckets help histograms; for flexible ranges use DAX or bins (12.6).
Revision card