Power BI Beginner Level: 12 - Adding Custom Columns

Table of Contents?|?Sample Input?|?Power BI Report File

Short summary:?In Power Query in the Add Column ribbon, you can use Custom Column to create a new column based on formulas similar to Excel and similar tools. Alternatively, under the same ribbon you can create a new column from existing columns.

In this article, I will show you two ways of adding columns in Power Query within Power BI. We will continue working on the processed data of the previous article. I want to have a new query but build on what we already have. To do that, I can reference the query of the previous article. In Power Query, make sure the queries pane is open (1), then right-click the query of the previous article (2), and then click Reference (3). This creates a new query which references the old one, so we don't mess with our previous result.

Alternatively you can load the result of the previous article as an Excel file.

Let us add a profit column as Sales amout minus Product cost. You have two options for doing that:

Option 1: Under the Add Column ribbon, click Custom Column.

Give the column the name “Profit” and set it as the [Sales amount] - [Product Cost].

You can choose from the available columns on the right, so you don’t have to type out their names. In general, you can use simple operations, including

• Arithmetic: Plus +, minus -, times *, and divided by /.
• Comparisons: greater than >, greater or equal >=, less than <, less or equal <=, equal =, and unequal <>.
• Checking for empty (= null to check if empty, <> null to check if not empty).
• You can also do if-then-else statements quite simply using the keywords if, then, and else; however, I will show you an easier way to do that in the next article.

Unfortunately, with Custom Columns, Power Query doesn’t automatically set the data type of the column, so please set it manually by selecting the icon (“ABC123”) to the left of the Profit column name and changing it to Decimal Number.

Option 2: You can achieve the same result by first selecting Sales amount and, while holding the Control key, then clicking the Product Cost column. You should now have both columns selected. Then, in the Add Column ribbon, you see all the possible operations you can do with the selected column(s), depending on the data types and numbers of columns. Note that From Text operations and From Date & Time operations are greyed out because we select two number columns. In the numbers section, click on Standard and then click Subtract to add the new column. Note that the order in which you select the columns matters here.

Make sure that your new column as a suitable name (“Profit”) and rename the steps so later on you still understand what you did here. It’s not so important in such a simple example but in practical projects you may have dozens of steps, so then it does make a difference. It is time well spend to help yourselves and other people out further down the line.

Next up, I will show you a handy way of creating conditional columns.

Please like, share, and subscribe and feel free to ask questions in the comments below.

Next article:?Adding Conditional Columns