Adding Custom Columns and Duration

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