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:
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.
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:
“>” – 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)?
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…
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.
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.