Hi. In this module, we'll show you how to work with conditional columns within Power BI. Now, often within a data set we need to test against two fields against each or test a field in a table against a target to work out KPIs. We often do this in Excel, and we might think of something like an IF statement within Excel, but in Power BI we can do this a lot more easily with the conditional column, without having to write a very long formula. So, let's see how we can do this. I've got some data here and this is just some sales data. I need to add a conditional column. If I want to do that, I've gotta go into my Power Query Editor. So, remember how we did this before. All I need to do is from the home tab I can go to the transform data button. If I click on that, that will just open up my Power Query Editor. So, when it's opened up, I need to go to the correct query, so I'm gonna go to tblAllSales, which is a data set that I loaded in earlier, and as you can see, I've got my fields on the right hand side. And I'm just gonna scroll across a little bit, and as you can see, we've got a couple of fields which say required date and shipped date. So, let's assume I need to create a column in this data set that will tell me whether the shipped date was after the required date. If the shipped date was after the required date, I want-, might wanna show the word ‘late’ in my column. If it was before the required date, then I might want to say the words 'OK.' There's that kind of logic there, so I can do this quite easily with Power BI. All I need to do is, from the Power Query ribbon I need to go to the ‘add column’ tab, and if I go to the add column tab there is a button here called conditional column. If I click on the ‘conditional column’, it brings up a dialogue box. And if you've ever done IF statements in Excel or in coding before, this should be pretty straight forward for you, but it's a much easier way of doing it, and this just provides a, a nicer way of doing or adding this kind of logic to your table.

First of all, we have to give the, the new column a name because we are gonna add a column to our table, but I'm gonna call it ‘Late or Not’. ‘Late or Not’. And then the logic comes below that. So, we've got the IF statement there, so we're gonna say, 'If the shipped date is after the required date, we'd like to display the word late.' So, column name, I'm gonna select Shipped Date, so IF Shipped Date, and then we have the Operator, and the Operator can be-, you might be familiar with these: ’equals’, ‘greater than’, ‘does not equal’. Got quite a few in there. You might have come across those in something like Excel. Gonna say, 'is after,' and then I'd like to select a, kind of, a-, I can type a value in or I can put in a date, but I might wanna select something from a-, from the table, so I've gotta change this dropdown to select a column, and if I change that to select a column it allows me to select a field from the table.

So, if I click on dropdown and I want that to be required date, we've added our logic. So, ‘if shipped date is after required date, then’ what do we want to happen? Well, I want to display the word 'late', so in the column it will display 'late' for that row. But the advantage of using our conditional column is that we can add many more different clauses, so I can keep adding, and I can, kind of, keep adding clauses without having to do too much formula work. And I can add as many as I like. In this case I'm gonna get rid of the extra clauses, and if you ever wanna get rid of the clauses you can go to three dots and delete. If it's the case that I wanna display the word late if it's-, if the shipped date is after required date, we've gotta have an alternative and you always have an ‘else at the bottom. So, if it's late we display 'late,' otherwise we might wanna display the words OK.' So, I will simply just click in there and type in OK.' So, I can add in further clauses but, for a simple kind of conditional formula, conditional column, we can do this.

We have two outcomes, all I do is click on ‘OK’, and what it'll do is it will add a column to the right and, where the dates-, where the required dates are before the shipped date it's gonna display OK, but if I scroll down there will be some records where the, the, the product was shipped after the required date, so it will display the word 'late.' So, that's how we add a conditional column to a Power BI table.

Using the data model and other Query Editor tools