Add A Custom Column In Power BI

Introduction

 

In Power BI Desktop, without much of a stretch, you can include another custom segment of information to your model by utilizing Query Editor. With Query Editor, you make and rename your custom section to make PowerQuery M equation inquiries to characterize your custom segment.

 

In the previous article ‘Removing Unnecessary Rows Using Power Query In Power BI’. I have shown you how to remove rows through power query. Here in this article, you can see how to add a custom column in power query. Follow the below steps.

 

Step 1

 

First of all, you need to open the power query editor window. Go to the home tab of Power BI desktop and click on ‘Transform data’. Your power query editor will be open.

 

 

 

 

Now go to ‘New source’ and import data from any source that you want. Here, I am using the ‘Excel sheet’ that contains the data of ‘Employees’. So click on ‘Excel’ and give the path of the file and open it into the power query editor.

 

 

Select the checkbox of ‘sheet1’ and click on ‘Ok’.

 

 

Once you click on ‘Ok’, you can see the employee table that has eight columns like ‘empno’, ‘ename’ and salary etc. like the below image.

 

 

Step 2

 

Now, I am going to add a ‘Custom Column’ in this table. To do so, go to the ‘Add Column’ tab and click on ‘Custom Column’. A new ‘Custom Column’ window will be opened. See the below images.

 

 

 

The Custom Column window

 

The  Custom Column window has the following features:

1. The name of your custom column, in the New column name You can rename this column as required.
2. A list of available columns on the right side.
3. Insert a formula in the Custom column formula. You create these queries by building the formula where your new custom column is defined.

 

Step 3 - Create formulas for your custom column

1. Select a column from the Available columns, and then click Insertbelow the Available columns to add them to the custom column formula. You can also add a column by double-clicking on it in the list.
2. As you enter the formula and build your column, note the indicator in the bottom of the Custom Column

For example, I am adding one column i.e. ‘Incremented_Salary’. My scenario is to increase salary by 30%. So, my formula should be ‘Sal+ sal*30/100’. See the below image.

 

 

If there are no errors, you'll see a green right mark and the message, 'No syntax errors have been detected.'

 

But, if there is any syntax error, you'll see a yellow warning icon below, along with a link to where the error occurred in your formula.

 

 

I added a closing bracket at the end of the formula. So, I got an error. Remove the extra bracket.

 

Now your formula is ready. So, click on ‘Ok’. The extra ‘Custom column’ with its value will be added to this table.

 

 

To save these changes, go to ‘File’ and click on ‘Close & Apply’. Your updated table will be shown in the Power BI desktop window.

 

 

Summary

 

I hope you understood this article. Try the above simple steps to add a custom column in the power query editor. Stay with us, and thanks for reading!