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.