How to Add Column Using Power BI Power Query Editor

While working on Power BI reports for clients, I noticed that the data coming from Excel, SharePoint, or databases was rarely ready for reporting. In many cases, the values I needed were not available as separate columns. Instead, I had to create new columns from existing data, such as calculating values, applying simple business logic, working with dates, adding index numbers, or fixing incorrect and inconsistent data.

To handle these scenarios, I used the Add Column feature in Power Query. It provides multiple options to create new columns without modifying the original data.

In this tutorial, I will explain how to add columns using Power BI Power Query. Throughout this guide, I will cover:

• Add Column Using Custom Column in Power BI Power Query
• Create a Conditional Column in Power BI Power Query
• Add a Date-Based Column in Power BI Power Query
• Use Index Column to Add a New Column in Power BI Power Query
• Add Column Using Advanced Editor in Power BI Power Query

Add Column Using Custom Column in Power BI Power Query

For this example, I am using a simple sales dataset that contains order-level details such as product name, sales amount, and cost.

In this dataset, there is no Profit column. To analyze profitability in Power BI, we need to create a new column using existing columns such as Sales and Cost.

To do this, follow the steps below:

1. Open Power BI Desktop and load the dataset using the Get Data option.

2. After the data is loaded, go to the Home tab and click Transform data. This will open the Power Query Editor, where we can clean and transform our data.
   

3. In the Power Query Editor, make sure the table containing Sales and Cost columns is selected. Go to the Add Column tab in the top menu and click Custom Column.

4. In the Custom Column dialog box, enter Profit as the new column name. In the formula box, enter the calculation:

[Sales] - [Cost]

5. Click OK to apply the formula.

Power Query will create a new column named Profit, showing the calculated profit for each order. If required, change the data type of the new column to Decimal Number or Whole Number.

Now, you can use the Profit column in visuals such as tables, charts, and KPIs.

This is how you can add a new column using Custom Column in Power BI Power Query to create calculated values without modifying the original source data.

Create a Conditional Column in Power BI Power Query

For this example, I will use the same sales dataset that contains Product Name, Sales, Cost, and the Profit column we created earlier.

Here, I want to classify orders based on profit value. If the profit is greater than 0, it should be marked as Profit; otherwise, Loss. This helps in quickly identifying profitable and non-profitable orders in Power BI reports.

Follow the Steps to Create a Conditional Column in Power BI Power Query:

1. Open Power BI Desktop and go to Transform data to open the Power Query Editor. Select the table that contains the Profit column.
2. Go to the Add Column tab and click Conditional Column.

3. In the Conditional Column window:
   • Enter Profit Status as the column name.
   • Set the condition as:
     • If Profit is greater than 0, then output Profit
     • Else output Loss
4. Click OK to apply the condition.

Power Query will create a new column named Profit Status, showing whether each order is a profit or a loss. Review the values and adjust the data type if needed.

Now, you can use the Profit Status column in slicers, tables, or charts to analyze profitable versus loss-making orders.

Add a Date-Based Column in Power BI Power Query

For this example, I am using the Super Store dataset, which contains the Ship Date column.

In this scenario, I will use the Ship Date column to extract the Month Name.

To do this, follow the steps below:

1. Open Power BI Desktop and click Transform data to open the Power Query Editor.
2. Select the table that contains the Ship Date column. Ensure the Ship Date column data type is set to Date.

3. Go to the Add Column tab. Expand Date, then select Name of Month.

Power Query will create a new column displaying the month name (January, February, etc.) based on the Ship Date. Rename the new column to Ship Month.

Now we can easily analyze sales data by shipping month using slicers and visuals in Power BI.

Use Index Column to Add a New Column in Power BI Power Query

While working on reports, there are situations where I need a row number, serial number, or a unique sequence to track records. For example, I want to display a serial number in a table visual or use an index value for sorting and reference purposes.

In this example, I will add an Index Column to the sales dataset to generate a sequential number for each row.

Follow the steps below:

1. Open Power BI Desktop and load your data using the Get Data option.
2. Go to the Home tab and click Transform data to open Power Query Editor.

3. In Power Query Editor, select the table. Go to the Add Column tab. Click the Index Column.
4. Choose from 1 (or from 0, based on your requirement).

A new column will be created with sequential numbers for each row. You can rename the column to Index or Serial Number.

This is how you can use the Index Column option to add a new column in Power BI Power Query.

Add Column Using Advanced Editor in Power BI Power Query

While working on Power BI reports, there are situations where the standard options, like Custom Column, are not enough. In such cases, you can use the Advanced Editor in Power Query to add a new column using M logic.

In this example, I will add a Profit column using the Advanced Editor in Power Query based on the existing Sales and Cost columns.

Follow the steps below:

1. Open Power BI Desktop and load the data using Get Data. Go to Home, click Transform data to open Power Query Editor.
2. In Power Query Editor, go to Home, click Advanced Editor.

3. In the Advanced Editor window, locate the last step of your query. Add a new step to create the Profit column, for example:

#"Added Profit Column" = Table.AddColumn(
    #"Previous Step",
    "Profit",
    each [Sales] - [Cost],
    Int64.Type
)

4. Make sure the new step name is used in the final line of the query.
5. Click Done to apply the changes.

Power Query will add a new Profit column calculated from the Sales and Cost columns.

This is how you can add a column using the Advanced Editor in Power Query for advanced data transformations in Power BI.

Conclusion

In this tutorial, I explained how to add new columns in Power BI Power Query using different methods. I covered how to create a column using Custom Column for calculations, how to use a Conditional Column to apply simple logic, how to add a date-based column from a date field, how to create an Index Column for row numbers, and how to add a column using the Advanced Editor for more control.

You may like the following Power BI tutorials:

• Merge Tables in Power BI
• Remove Leading Zeros in Power BI
• Convert Number to Text in Power BI
• Count Rows and Distinct Values in Power BI Power Query