Custom columns in Power Query

1. Custom columns in Power Query

Welcome back! I'm hoping you're enjoying the course so far. My name is Maarten Van den Broeck, and I'll be your help for this part of the course. Custom columns are one of the most flexible tools you will use in Power Query. By using them, you will be able to modify your tables almost like an Excel spreadsheet. In this lesson, we'll also start to learn a little bit about the heart of Power Query: M language.

2. Custom columns

Custom columns are an extremely flexible tool in Power Query. They allow you to modify your queries by writing in a simple scripting language called M language - which comes from data Mashup. This is somewhat similar to DAX language but has some key differences such as the fact that it is case-sensitive. We'll learn more about M in the next lesson. Previously, you learned to apply numerical transformations such as adding a constant value to your column, but it wasn't possible to sum up two columns into one new column. However, by using custom columns, you are able to do this and much more. We can even extend the functionality of the conditional column feature by using logical operands such as AND, OR, and NOT to check several columns for our desired conditions. Once you understand custom columns, it will quickly become the most frequent feature you will use in Power Query.

3. Operations for custom columns

There are several basic operations that can be used when creating custom columns. You can use basic arithmetic operations to combine numerical columns together by adding, subtracting, multiplying, dividing, or exponentiating them. This can be useful if you are trying to compute total sales from a quantity and price column, like we see in this example screen shot. Text columns can be concatenated together with the & operation. This will work similarly to the merge columns transformation you learned earlier. You can also use comparative logic operations such as less than and greater than to compare column values together. Together with conditional logic operations such as "and, or, and not", you will be able to build some complex conditional columns that you couldn't make just by using the regular conditional column feature in Power Query. You must be mindful of the data types of the columns that you are using, as any type mismatches are likely to result in an error. In addition, if any rows have a null value in either column, the resulting custom column will also contain a null value for that row.

4. Group by with custom columns

A very powerful application of custom columns is combining them with the Group by transformation which you've already learned previously. This transformation usually aggregates the values in a certain column using an aggregation function such as sum or average. However, using the "All Rows" aggregation allows you to apply advanced transformations with custom columns. The "All Rows" aggregation divides your data into several smaller tables, according to the grouping that you selected in your group by function. Together with custom columns you can alter the data in these tables using Table M functions. A good example of this is being able to group sales data into product categories, then ranking each product according to sales performance in its own category. This may sound difficult to accomplish at first, but you'll find that it can all be done in a few steps by using custom columns in Power Query.

5. Let's practice!

You will apply these concepts to Northwind's inventory data to prepare it for analysis.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.