Power BI – How to Add Conditional Columns?

Microsoft Power BI is a software program for Interactive Data Visualization with a strong emphasis on Business Intelligence. Power BI is a collection of software services, programs, and links that combine to turn unrelated data from various sources into coherent, aesthetically pleasing, and interactive insights. With Power BI Desktop, you can connect to the world of data, create foundational reports that are appealing, and share your work with others so they can build on it and advance your business intelligence initiatives.

Users can import and reshape data from a number of Microsoft applications, including Excel, Power BI, Analysis Services, Data verse, and others using Power Query, data connectivity, and data preparation tool. You can use Power Query in Power Query Editor to generate new columns whose values depend on one or more criteria that have been applied to existing columns in your table. 

How to seek Power Query Editor?

The Data Table used in the article has been formed from the Dataset of a Library Supplies company. Below is the screenshot of the dataset as well.

If you are witnessing the below-shown interface of the Power BI Desktop then you need to open the Power Query Editor window.

Go to the Transform data Tab and select Transform data.

A new Power Query Editor window would be displayed, where you can Add Upload the dataset by selecting New Source.

Data Table will be created in Power Query Editor as follows-

Adding a Conditional Column

On the Add column tab, in the General group, there is Conditional column command.

Here, we are targeting the “Unit Price (INR/Unit)” column from the Data Table to create a conditional column “Display Price”. The condition is, if the Unit Price is less than or equal to 100, we will set the Unit Price as 150 else the original price would be unchanged, and all the changed or unchanged values would be inserted into the “Display Price” column respectively.

The output of the query will add the conditional column.

Adding a Column by Incorporating Multiple Conditions

Now let’s add another conditional column named “Final Price”, that will contain the “Unit Price (INR/Unit)” column value, if it is less than or equal to 200, set it to 500. Else if it is greater than or equal to 500, Store the Unit Price. However, the figure from the “Total Price (INR)” column will be the Output if none of the earlier tests returned a positive result.

The output of the query will add the conditional column.