How To Add A Custom Column With Different Values In Power BI

Introduction

 

I have already written an article for adding custom column in Power BI.

 

But here in this article you will learn how we can add different values in a single column corresponding to another column. For example – Suppose we have a city table and we need to add a pin-code column in this table and insert values as per city names.

 

See the below steps to understand the task and how to solve this problem.

 

Step 1

 

I have a city table having two columns, City and State. Values are like this,

 

 

I have imported this table into Power BI. Check here to know how to import data from excel to Power BI.

 

 

Step 2

 

Now open query editor to add custom column. Click on ‘Transform data’ to open query editor window.

 

 

Step 3

 

Go to ‘Add column’ tab and click on ‘Index column’. First, we will add index column so that we can insert unique value as per the city.

 

Note

Indexing is important if you will get data according to your sequence. Suppose you would like toput the  first value in the third place or the second value in the first place.

 

So, I am adding index column first.

 

 

Step 4

 

Now click on ‘Custom Column’, a new window will be opened. Provide the name of new column. I am giving ‘Pincode’ as my new column. Now I am giving a formula to provide values in this column.

 

Formula will be given below, (if [Index] = 0 then 226001 else if [Index] = 1 then 411001 else if [Index] = 2 then 281001 else if [Index] = 3 then 248001 else 451010)

 

 

Click on ‘OK’ button. You new custom column will be added.

 

 

Note 

We have added index column just for indexing. Our task can be done with the ‘City’ column also. But suppose if you want Mathura as your first city and Lucknow as third city.

 

So, you can do it with the index column. So, I have shown you our with indexing as it will be helpful for such conditions.

 

CIick on ‘Close & Apply’ in ‘File’ tab.

 

 

 

Summary

 

I hope you understand the condition and solution of this condition. We can also do this task by adding ‘conditional column’ instead of ‘Custom column’ :

 

 

This will also gives you the same result. But our motive to solve it with indexing is mentoned in “Note 2”. Thanks for reading.