A conditional column is a column that is generated based on a condition imposed on an existing column in data model. In general, conditional column is computed from other columns or values. You can create a conditional column in Power Query Editor.
Lets see how to create a conditional column in Power BI.
As you can see, here we have a sample data set.
Now we will add a new column in table based on some condition check on Aging column.
Lets create a condition if Aging value is less than 2 then display “Fast delivery”, if Aging value is greater than 2 and less than 5 then “On time delivery” and if Aging greater 5 then “Late Delivery”.
Now click on Transformation Data to open Power Query Editor.
After that, click on Conditional Column under Add Column tab.
Once you click on conditional column tab, a Add Conditional column dialog box opens as shown below.
This dialog box helps you to create a if else expression as shown below.
if condition then expression else expression
In the New column name box, specify a unique name for conditional column, Lets give it a name DeliveryStatus.
Next, In the Column name list box, select a column that you want to use in your condition check.
Here we will select Aging column.
In the Operator list box, select an operator and in Value box, specify the conditional value.
Here we will select less than equal to operator and value as 2 to specifying the condition if aging is less than or equal to 2.
In the Output box, specify the output value that your conditional column will return when the if condition is true.
Here we will give the value “Fast Delivery”, If aging value is less than or equal to 2.
In case, if you want to add an else expression when the condition is false, you can click on Add Clause.
Lets click on Add Clause to add one else if expression, to specify the condition if Aging is greater than 2 and less than 5 then “On time Delivery”.
At last, add a final Else expression (Optional), You can enter a value, another column, or a parameter.
Lets specify the final value that is “Late Delivery”, it will be displayed for aging value greater than 5.
Once you are done with this, click on OK button. Power BI Desktop adds your conditional column to the data model also adds the Added Conditional Column step to your query’s Applied Steps list in Query Settings.
You can see, the value for DeliveryStatus column is generated based on values in Aging column.
In case, if you want to modify the Conditional column, you can double click on Added Conditional Column step in Applied Steps under Query Settings.
It will open a same conditional column dialog box, here you can rename, or modify conditions.
After that, just click on Close & Apply tab to save your changes.
Lets see, the output of conditional column in report.
Also Read..
