Adding Custom Columns and Duration

Adding Custom Columns and Duration

Previous Session Summary

In the previous lesson, we looked at how we can convert data into the Date or DateTime datatype. Once that conversion is done then we looked at how we can extract various pieces of information from the date column including the start date of a month, the name of  a month or the week of a year.
We also reviewed how to duplicate a column, move a column and rename a column.

Lesson Synopsis

In this lesson, we move on from looking at dates and look at the duration function: how to quickly change time data into different durations. Before we get into that though we’ll learn about creating a new column of data from pre-existing columns of data.

Resource Listing

The Video

Play Video

Lesson Notes

In this lesson we’re going to have a look at two topics; adding a custom column and the duration function.

What is the Custom Column function?

The custom column function allows us to create new columns into our dataset that we don’t already have. You can create columns in the following ways:

  1. Use columns that are already in the dataset
  2. Solely from text that you type into the column. Ensure you encase the text in speechmarks. The same text will appear on every row of the column
  3. Solely from numbers that you type into the column. The same number will appear on every row of the column
  4. A mix of data from columns that are already in the dataset and text or numbers that you type in

When we get more advanced with our PowerQuery skills, we can also use a number of PowerQuery specific functions in our Custom Columns and we’ll look at introducing that topic later on in the course.

If you want to introduce some conditional logic, so for example; if Column A = “B” then “C” but if Column A = “D” then “E”, we can use the custom column function to create these type of columns but it’s easier, at least for beginners, to use the ‘Conditional Column’ function and again we get to this topic later on in the course.

How can I create a Custom Column?

There might be lots of different things that you might want to put in your new column. The below describes how to use only columns that are already in your dataset as well as an ‘operator’ to create a new column. In our video, we work out the difference between two date columns by subtracting one column from another.

  1. Select the Add Column tab.
  2. Select the Custom Column function in the General section of the ribbon
  3. In the resulting pop up menu there are three boxes: One to name the column, a formula box to dictate what should go in the new column and a box which shows the columns already in the data sets.
  4. Type the name that you’d like the new column to have in the ‘New Column Name’ box.
  5. Click into the ‘Custom Column Formula’ box and then select one of the columns to be used in the formula from the ‘Available columns’ box. Select ‘Insert’. Repeat the selection with the second column.
  6. You should now have the two columns that you’d like to include in your formula in your ‘Custom Column Formula’ box each surrounded by square brackets.
  7. In between your two columns, after the closing brackets of the first column and the opening of the second input a subtraction operator.
  8. Click Ok.

Operators

Now is a good time to go through operators. Operators are signs used in Excel as well as PowerQuery and Power BI to command each programme to either:

  1. logically compare one number against another and include;

 

“>” – the greater than operator, identifying where one number is greater than another

“<” – the less than operator, identifying where one number is less than another

“=” – the equal to operator, identifying where one number (or text item) is equal to another

“>=” – the greater than or equal to operator, identifying where one number is more than or equal to another

“<=” – the less than or equal to operator, identifying where one number is less than or equal to another

“<>” the except for operator, identifying where a number (or text value) is explicitly not equal to another

 

2. perform arithmetical calculations including:

 

“+” – Addition

“-“ – Subtraction

“/” – Division

“*” – Multiplication

There are also other miscellaneous operators including the ampersand operator (“&”) which joins together text strings.

The chances are if you’ve been using Excel for a while, then you probably know all of these already!

How can I join together the text from two columns (concatenation)?

  1. Select the Add Column tab.
  2. Select the Custom Column function in the General section of the ribbon
  3. Type the name that you’d like the new column to have in the ‘New Column Name’ box.
  4. Click into the ‘Custom Column Formula’ box and then select one of the columns to be used in the formula from the ‘Available columns’ box. Select ‘Insert’. Repeat the selection with the second column.
  5. Between the two columns insert an ‘&’

What is the duration function?

The duration function allows us to convert a column of decimal numbers, or alternatively a column that has been created by subtracting one date column from another into units of time. This can be really handy for working out the period of time between two dates.

 Columns of whole numbers will not work for the duration function as a single whole number is considered to be 1 day.

So how can we use the duration function?

Well option number 1.

If we’ve created a custom column by subtracting one date column from another date column…

  1. Highlight the column you wish to convert
  2. Go to the ‘Transform’ tab
  3. On the Date & Time Column section of the ribbon, select the ‘Duration’ drop down
  4. Select the option that you’d like.

What are your options?

Days/ Hours/ Minutes/ Seconds – Extracts the day, hour, minute or second segment from the duration column and discards any other part of the data

Total Years/ Total Days/ Total Hours/ Total Minutes/ Total Seconds – Expresses the total amount of time in years, days, hours, minutes or seconds.

Multiply/ Divide – Multiplies or divides the amount of time in each row by a number that you specify

Statistics – You’ve got various options from here including totalling the amount of time, identifying the average, minimum or maximum value.

Option number 2

Option number 2 is pretty much the same as option number 1, but you need to convert the column of data into the Duration type first. You need to do this when you have a column of decimal numbers.

  1. Highlight the column you wish to convert
  2. Go to the ‘Transform’ tab
  3. Go to the Data Type and select Duration
  4. On the Date & Time Column section of the ribbon, select the ‘Duration’ drop down
  5. Select the option that you’d like

.... and a little more!

Using the ampersand (&) with numbers – Using the ampersand sign to connect columns together only works if the columns are text. If one or both columns are number datatype columns then using the ampersand will result in an error. If you want to concatenate a number column then first wrap the name of the column in square brackets in a Text.From() function.  See the this link for Microsoft details on this function.

Merging – In our video, we join together the text of two columns using a custom column and an ampersand sign along with a space inbetween two speechmarks. We then remove the two columns that we don’t need. An alternative option is to use the merge function. Highlight the two (or more) columns that you want to merge, then right click either of the headers and select Merge from the menu. You can opt to select a delimiter to go between the text of the two columns. The columns will then be merged in the order that you highlighted the columns. You also won’t need to delete the constituent columns as the original columns are merged.