Problem
In this article, I’m going to discuss how to create a conditional column in Power BI. A conditional column is just another column that is generated based on a condition imposed on an existing column. A simple use case would be to categorize users as Child or Adult based on age.
Solution
In this tip, I’m going to create a Power BI report that will fetch some demo user data from a CSV file. The CSV file has only two columns – Name and Age. After importing this data into Power BI, we will categorize the users as Adult or Child based on the age. If the age of the user is greater or equal to 18, then he/she should be categorized as an adult otherwise as a child.
Importing Data into Power BI
The first step to begin with is to import the data into the Power BI environment. To do this I’ll follow the steps below.
- Click on Get Data from the ribbon bar in Power BI Desktop.
- The Get Data dialog appears.
Select Text/CSV under Files and click Connect.
Browse the location of the CSV file and click OK.
The preview of the data to be loaded appears. Click Load.
Adding Conditional Column to Power BI
Once the source data is fetched into Power BI, the next step is to go ahead and create the conditional column that we have discussed earlier. I’ll follow the steps below to create the conditional column.
Click on the Edit Queries from the ribbon on the top. The Power Query Editor window appears.
Select Add Column on the menu bar and select Conditional Column. The Add Conditional Column dialog appears.
Enter the New Column Name as “Category“.
From the Column Name dropdown, select Age (since our conditional column is based on Age).
From the Operator dropdown, select “is greater than or equal to”.
Now, enter values as “18“, “Adult” and “Child” for the fields Value, Output and Else respectively and click OK.
Notice that the new column Category has been added to the dataset. We also have values (Adult/Child) in this column based on the age of the users.
Creating Power BI Chart Using Conditional Column
Once we have the conditional column ready in Power Query Editor, we can easily create visualizations using this new column. Let’s analyze the Users count by Category.
Select Pie Chart from the visualizations. Drag and drop Category to Legend and Users to Values.
From the pie chart, we can infer that more than 60% of the users are adults while the rest are children.
Next Steps
- Read more about creating conditional columns in Power Query.
- Check out all of the Power BI tips on MSSQLTips.com.
