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

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Stay Connected

If you'd like to be notified when I write a new Excel article, enter your name and email and click SUBSCRIBE. You can unsubscribe anytime, and I will never sell your email address.

Want to learn Excel?

Our Campus Pass includes access to our entire Undergrad and Masters catalog. Gamification ensures it is the most fun you can have learning Excel :)

5 Comments

  1. Shims on September 15, 2020 at 1:49 pm

    I prefer using formulas so I can always look back at a later time and see the trail of what & how I created. In Power Query is that possible, or it just does it and that’s it?

    • Jeff Lenning on September 15, 2020 at 1:52 pm

      When using Power Query, you can always open the Power Query Editor to inspect the details of each step and each calculation.
      Thanks
      Jeff

  2. Alex Blakenburg on September 16, 2020 at 12:12 am

    Having more of a database background, I would be inclined to add a date last refreshed column to the query results unless someone knows a better way to determine if a query is “up to date”. Relying on queries needing to be refreshed is a bit like having calculations set to manual.

    #”Added Custom” = Table.AddColumn(#”Renamed Columns1″, “LastRefreshed”, each DateTime.LocalNow()),
    #”Changed Type1″ = Table.TransformColumnTypes(#”Added Custom”,{{“LastRefreshed”, type datetime}})

  3. Geoff Stuart on September 16, 2020 at 8:33 am

    Jeff,

    This was a great practical intro to Power Query. Microsoft never seems to want to publish simple examples of their functionality (too many engineers writing the marketing literature!)

    But this was excellent. I’ll be diving into your site to find more tutorials on this because I have several pressing requirements that Power Query can help with. Thanks!

    Geoff (which is the correct spelling of the name)

    • Jeff Lenning on September 16, 2020 at 8:54 am

      Geoff,
      Thanks for your kind note, I really appreciate it!
      There are so many applications for Power Query, and this is just another example.
      Here is a list of my other PQ posts in case you want a convenient way to check them out:
      https://www.excel-university.com/tag/power-query/
      Thanks
      Jeff (the other way to spell it)

Leave a Comment





For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.