Create Calculated Columns without Writing Formulas

Historically, we have created calculated columns by writing formulas. In this post, we’ll see that in some cases, clicking a few buttons in Power Query can be a nice alternative.

Objective

Before we get into the mechanics, let’s confirm our objective. Let’s say we have a table full of transactions, like this:

We would like to create some calculated columns to the right of the SKU column. For example, we may want to compute the month number of the transaction, the total of the Amount and Shipping columns, the amount of sales transactions (Type=S), the amount of refunds (Type=R), and the product group (the middle code contained within the SKU). Something like this:

We can add all of these calculated columns without writing traditional Excel formulas … we can create them by clicking a few buttons in Power Query. Let’s dig into the details.

Details

We’ll walk through the process using these steps:

• Import the data into Power Query
• Create the calculated columns
• Send the results to Excel

Let’s get to it.

Import the data into Power Query

First, we need to get the transactions table into Power Query. Since our table is already in Excel, we’ll select the data table and click the Data > From Table/Range command. Excel opens the Power Query editor and shows a preview of the data:

Note: in practice, you can connect to other data sources by using the corresponding Data > Get Data command.

Now that the data is in Power Query, we can click some buttons to create the desired calculated columns.

Create the calculated columns

We can use the commands on the Add Column tab to create calculated columns in Power Query. Let’s walk through the steps for each of the calculated columns we want to create.

Month

To create the Month column, we begin by selecting the current Date column, like this:

Then, we click the Add Column > Date > Month > Month command. Bam, we have a new Month column like this:

Note: there are many options to explore in the Add Column > Date command, including Year, Start of Year, End of Year, Month, Name of Month, Start of Month, End of Month, and more.

Total

Now let’s create the Total column. We want to add the Amount and Shipping columns. The first step is to select both the Amount and Shipping columns, like this (hold down Shift or Ctrl to select multiple columns):

Then, click the Add Column > Standard > Add command. Bam … our new column appears:

Note: there are many options in the Add Column > Standard command, so definitely check them out as they may come in handy.

We can rename the new column by double-clicking the current label (Addition) and entering the new name as desired, such as Total:

Sale

We would like to create a new column that will show the Total amount for sales transactions. The type of transaction (Sale/Refund) is noted in the Type column:

If the Type column value is S, it is a Sale transaction and we want the Total amount to appear in our new Sale column.

To create this column, we use the Add Column > Conditional Column command. In the resulting dialog, we enter the desired logic. In our case, the logic is: if the value in the Type column is equal to S, then we want the new column to display the value in the Total column, otherwise, display 0:

Note: you can use the Value, Output, and Else buttons to toggle between referring to a specific value (such as S or 0), or referring to a Column (such as Total).

We hit OK, and bam:

Refund

We basically use the Add Column > Conditional Column command again to create the Refund column. If the Type value is equal to R, then we want the value in the Total column, otherwise 0. Bam:

Group

The final column will display the product group code. The product group is the middle code in the SKU column. Let’s take a look at the SKU column:

When we look at the values in the SKU column, we notice that each SKU has three parts, separated with a dash. Since each SKU segment appears to contain a variable number of characters, we’ll extract the Group code based on the dash delimiters.

After selecting the SKU column, we use the Add Column > Extract > Text Between Delimiters command.

Note: the Add Column > Extract command contains many options, so you’ll definitely want to check them out as they may come in handy depending on your data.

In the resulting dialog, we enter the dash delimiters:

Note: you can click Advanced options to reveal many additional settings that may come in handy.

We click OK and bam:

We can double-click the column label and enter our desired column name, Group:

With our calculated columns in place, it is time to send the results into Excel.

Send the results to Excel

To send the results to Excel, we use the Home > Close and Load To command.

In the resulting dialog, we opt to send the results to a Table in a New worksheet … and bam:

Yay … we did it!

Conclusion

We created several calculated columns without writing any traditional Excel formulas. We could have created them with traditional Excel formulas and used functions such as MONTH, SUM, IF, MID, FIND, LEN, and so on. But, clicking buttons in Power Query is a convenient alternative.

Plus, the really cool part about this solution is that next period, we when have a new or updated transactions table, all we need to do is right-click our results table and click Refresh. Power Query will process the columns again and update the results table accordingly.

If you have any other options or cool Power Query techniques, please share by posting a comment below … thanks!

Sample File