Sign in or start a free trial to avail of this feature.
9. Custom and Conditional Columns
You can add columns to your dataset in the Query Editor. In this lesson, we will see how you can add columns using a formula or using IF/ELSE conditions.
- Custom columns allow you to add new columns to a query using a calculation or a formula
- Custom columns are written using M formulas
- You may prefer where possible to import the data into Power BI Desktop and then create new columns there
- Conditional columns let you create a new column using IF/ELSE logic
- You can create multiple conditions using any of the columns in your query
- Conditional columns can be useful as the relevant formulas are created for you
So far, we've assumed the data we want is either available in the current data set or another data set we can query. However, this isn't always the case and we may want to add new data to our current query. In this lesson, we'll look at two methods for adding new columns using formulas, Custom Columns and Conditional Columns.
We'll start with Custom Columns. We'll navigate to the add column tab, and select Custom Column. We now need to enter a name and formula for the new column. We will call this column Unit Price and we'll enter a formula that gives us the average selling price for each row. This formula is simply sales divided by quantity. When creating our formula, we can select columns from the list on the right. This tool also automatically informs us if there are any syntax errors with our formula.
Unfortunately, the query editor does not use a tax language that's used elsewhere in Power BI. Instead, it uses a formula language called M. The vast majority of the work you do in the query editor will not require the use of M, so we won't go into it in too much detail. However, we will cover the basics in the next lesson. If you're familiar with DAX, you may prefer to simply input your data to Power BI and use the DAX formulas there.
In this case, we are only using simply arithmetic so there is no problem with understanding the formula. We'll press OK, and see that the new column is added to the query. As we can see, adding a Custom Column is easy for simple calculations such as this. Let's move on and look at Conditional Columns. If you have experience with Excel, DAX, or any other programming or formula language, you've probably used conditional functions such as IF and ELSE. In the query editor, we can create a column based on one or more conditions very easily. Let's consider the Customer ID column. Suppose we want to divide our customers into groups based on their Customer ID. Let's say we want to identify those customers whose ID starts with A10.
We'll navigate to the Add Column tab and select Conditional Column. We'll name the column Customer Group and then add the condition. Our condition is based on the Customer ID field, so we will select that as the column name. Note that the operator varies depending on the type of the field. However, the options are generally similar to the filtering options we saw previously. In this case, we want to identify Customer ID's that start with A10, so we will select begins with and then type A10.
We'll set the Output as A10.
This will appear for any matches. We will now set a value for Otherwise. In this case, A1X.
If the Customer ID does not start with A10, the customer's group will be A1X. We also have the option to add more rules by selecting the Add rules button. This would let us create more complex conditions based on multiple columns or divide the data into more than two groups. We'll use just one condition for now. We'll click OK and the new column is created. For rows where the Customer ID starts with A10, the new column has the value A10. For all other rows, it has the value A1X.
As we can see, Conditional Columns are easy to create, require no formulas, and can be quite powerful due to the extensive filtering options available. We've now seen how to easily create columns in the query editor. These columns did not require us to use any advanced M formulas, but it can be useful to have some knowledge of M. In the next lesson, we'll take a quick look at the M Formula language.