Add Conditional Column In Power BI Using Power Query

Introduction

 

Here in this article you will learn one more feature of Power Query. I will add a new column in my table using this new feature i.e. ‘Add Conditional Column’. In my previous articles ‘Add A Custom Column In Power BI’ and ‘Add A Column from Example In Power QueryEditor’ , I have taught you how to add a ‘Custom Column’ and ‘A column from Example’.

 

To do so, we have to open Power query editor by clicking on ‘Transform Data’ option at the ‘Home’ page of the Power BI tool. After opening the Power query window click on ‘New resource’ to import file. To check how to import file in power query you can see above mentioned links of articles.

 

Follow the below simple steps to add a conditional column using this new feature.

 

Step 1

 

I have imported a CSV file in power query editor. This CSV file contains the details of credit card like CreditCardID. CardType, CardNumber, ExpMonth etc. See the below image.

 

 

In this image you can see that we have a numeric value in ‘ExpMonth’ column. So, I will add a one more column in this table that shows the Value of these months in string.

 

For example, if ‘ExpMonth’ column is having 11, 8 etc. then the new column will show ‘November’, ‘August’ respectively.

 

Step 2

 

Now go to ‘Add column’ tab and click on ‘Conditional Column’. A new ‘Add Conditional Column’ window will be open.

 

 

 

Here in this window you can get the below options,

• New Column Name
  Enter the name of the new column in this text box. I will add ‘MonthOfExp’.
  
  
• If Condition
  Here we will mention the if condition, choose the column name, operator, and value (you can enter default value or or value from any column), then give the output value (you can enter default value or or value from any column).
  
  
• There are three dots …(more) option to delete the if condition.
  
  
• Add Clause
  You can add two or more if conditions here
  
  
• Else
  Here you can add else condition if required.

Step 3

 

I am giving the if codition as per my requirement that I have mentioned in step 1. As we have 12 month in a year, I will add 12 clauses of if condition. For Example

 

Column Name Operator Value OutPut

 

ExpMonth = 11 November.

 

 

 

I have added all 12 conditions in if clause so, My ‘Else’ clause is empty. If you want to use this clause you can mention only 11 months of conditions in the  ‘If’ clause and the rest  mention in ‘Else’ clause.

 

After mentioning all these conditions click ‘Ok’. Now you can see that a new column will be added in your table according to your condition. See the below image:

 

 

 

Similarly you can try other conditions and apply.

 

Column From Example

 

In the “Add Column’ tab we have the first option; i.e., ‘Column From Example’. See more about this in the previous article ‘Add A Column from Example In Power QueryEditor’

 

I have shown you how to add the column ‘From selection’, but we have one more option here; i.e., ‘From All Columns’. Let’s have a look at it.

 

This option is used if you want data in a new column with the combination of other columns. For example, suppose all types of Credit Cards will expire on the 25^th of every month. So, I will add the new column ‘DateOfExp’ and my data will be like this- 25-‘ExpMonth’-‘ExpYear’.

 

Let’s try this feature. Go to ‘Add column’ tab and open the ‘Column from Example’ drop down menu, you get the ‘From All Columns’ option. Click on it.

 

 

 

 

Enter the sample value in this column. For example—25-11-2006(25-‘ExpMonth’-‘ExpYear’)

 

 

 

After entering the one sample value press ‘CTRL+ENTER’. You will see rest of the fields will be entered automatically as per the above condition.

 

 

 

Rename this column by double clicking on it and click on ‘Ok’

 

 

 

 

Save these changes by clicking on ‘Close & Apply’ inside the ‘File’ tab.

 

Summary

 

I hope you enjoyed this article. Follow these simple steps and try to add new columns in your table like this. Thanks for reading.