1. Custom columns demo
As the name implies, custom columns are very customizable and have a wide array of uses. Once you master the concept of using them, you will quickly find that they will become one of the most flexible tools in your arsenal for preparing data.
In this demo, we’ll be looking at a sample of sales data from the Northwind traders dataset that you’ve been working with.
We are interested in analyzing the sales value for the orders, however, we are provided with a UnitPrice column, a Quantity column, and a discount column. Therefore our first step should be to add a column to our data containing the gross sales value, which is the UnitPrice multiplied by the Quantity. Let’s add a custom column:
You can type the column name, then press tab to auto-complete or you can double-click the column name in the available columns window to the right here. Don’t forget to name your column as well.
Now you’ll notice that the column doesn’t have a specific data type, this is important to note. By default, any custom column you create will have the data type “Any” so make sure to change it before you load your query into Power BI.
We need to factor in the discount percentage now, to arrive at a net sales figure. We can add another column, or we can modify the formula for the custom column we just created by clicking on the gear icon.
Remember to use the parentheses here as the order of operations does matter when you are creating custom columns.
Another great feature of custom columns is the ability to implement complex conditions using logical operations like AND, OR, and NOT as well as use a formula as an output for a conditional column.
Let’s say that there was a special offer that was running for this period of sales where customers that bought more than 50 units of the same product received an additional 5% discount for that product. However, this offer was not applicable to condiments or confections. We can implement this using a custom conditional column.
Once again, let’s add a custom column.
This custom column checks for 3 conditions at once for each row in your dataset, and also outputs a formula if the conditions are all true, both of which are not possible with the regular conditional columns feature in Power Query.
Finally, let’s look at combining the group by feature in power query together with custom columns. We’ll use the all rows aggregation in the group by operation:
This results in a table that contains two columns, one with the categories, and another that condenses all the columns in our previous dataset.
We can see a preview of this data by clicking on any of the cells in the Grouped column.
We want to rank each order by the highest sales in each product category. In order to do this, we must sort the condensed tables, then add an index column. We’ll use the Table.AddIndexColumn and Table.Sort functions to do this:
It may look like nothing happened at first but if we go to the new column we added and preview the table in one of the rows, we can see that the ranking column was added successfully:
Once you get the hang of writing M functions, which we will cover in the next lesson, you will find that with custom columns the possibilities are truly endless.
2. Let's practice!