Ravindra BagaleCourses & study guides मराठी Track your progress

8. Adding Columns: Power Query Add Column Tab and DAX Calculated Columns

8.1 The Add Column Tab at a Glance

By Last updated:

Start with a problem

Shahrukh wants a Net Amount column but keeps changing the original Amount column by mistake.

Explain simply

The Add Column tab always creates a new column and keeps the original. Groups: General (Column From Examples, Custom, Invoke Custom Function, Conditional, Index, Duplicate), From Text, From Number, From Date & Time.

Show the data

Order ID City Amount Discount Delivery Time Mins Order Status
201 Pune 500 50 8 Delivered
202 Nashik 300 0 12 Delivered
203 Pune 700 100 9 Delivered
204 Kolhapur 400 0 18 Delivered
205 Nashik 0 0 null Cancelled

About this data: This table uses columns: Order ID, City, Amount, Discount, Delivery Time Mins, Order Status. This table uses columns: Order ID, Amount, Discount, Net Amount. This table uses columns: Group, Commands. 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

  1. Load column_vs_measure_orders.csv › Transform data.

  2. Select Amount and Discount (Ctrl-click) › Add Column › Standard › Subtract.

    Your result should look like this:

    New column
    Subtraction: 450, 300, 600, 400, 0

  3. Rename it Net Amount; set type Fixed decimal.

  4. Check: Amount is unchanged.

Show the result

Your result should look like this (fictional sample):

Order ID Amount Discount Net Amount
201 500 50 450
202 300 0 300
203 700 100 600
204 400 0 400
205 0 0 0
Total 1,900 150 1,750

Explain what happened

Add Column wrote Table.AddColumn(..., each [Amount] - [Discount]) and kept both inputs.

Explore further

  • Transform-tab versions of the same commands change the selected column instead.

Detailed reference for this lesson:

Open the Power Query Editor (Home › Transform data) and click the Add Column tab. Everything here creates a new column and keeps the original (unlike the Transform tab, Module 6.3).

Group Commands
General Column From Examples, Custom Column, Invoke Custom Function, Conditional Column, Index Column, Duplicate Column
From Text Format, Merge Columns, Extract, Parse
From Number Statistics, Standard, Scientific, Trigonometry, Rounding, Information
From Date & Time Date, Time, Duration
AI Insights Text Analytics, Vision, Azure Machine Learning (need Premium/Fabric capacity; not covered here)

Power Query column or DAX column?

A good rule: create columns as far upstream as possible, in the source database, then Power Query, and only then DAX. Power Query columns are compressed better and keep the model simpler. Use a DAX calculated column fakt when you need model features such as RELATED across relationships (संबंध) or values that depend on other tables in the model (8.11).

Ravindra Bagale's Tip

Friends, many students add the same column in Power Query and as a DAX calculated column, and later forget which one the report uses. Decide once: static row-level columns go in Power Query. Delete the duplicate so only one version exists. Keep this in mind!

Fix common mistakes

Wrong result Reason Correction
Amount values changed Used Transform › Standard Use Add Column
Discount − Amount (negative) Selected Discount first Select Amount first
Net Amount is text Type not set Set Fixed decimal

Practise independently

Practice task

What is the total discount and total net amount?

Hints
  • Sum the columns.
Solution

Discount ₹150; Net ₹1,750.

Think like a professional

Keep raw columns until you are sure they are not needed – auditors ask for them.

Ravi Sir's Cards for this lesson

The Add Column tab at a glance. Raw data rarely has every column a report needs, like day name or GST.Revision card

मराठी cards