Adding Logical Statements into Conditional Columns

Private: PowerQuery for Finance Teams Adding Logical Statements into Conditional Columns

Adding Logical Tests to Conditional Columns

Previous Session Summary

In the previous lesson, we looked at adding in conditional columns. A newly created conditional column goes down a table row by row and populates (fills in) each row depending on other values on the same row in the table.

Lesson Synopsis

In this lesson we’re moving the idea of conditional columns on a smidge. In the last lesson we only had a single logical condition to test in our conditional column; something like:

If so and so is true then put in value 1, otherwise, put in value 2.

In this class, we’re going to add in some additional conditions so we’ll end up something like this:

If so and so is true then put in value 1, otherwise,
if so and so is true then put in value 2, otherwise,
if so and so is true then put in value 3, otherwise, put in value 4.

In Excel speak, as you may know, this is called a nested if statement.

Resource Listing

The Video

Play Video

Lesson Notes

In the last lesson we looked at creating a really basic conditional column which is like an IF statement in Excel. This conditional column tested a single logical condition and then returned a value based on whether the logical test was true or false. That would then look something like this:

If so and so is true then put in value 1, otherwise, put in value 2.

In this lesson we’re gong to look at adding in additional logical tests into that conditional column. That then would look something like this:

If so and so is true then put in value 1, otherwise,
if so and so is true then put in value 2, otherwise,
if so and so is true then put in value 3, otherwise, put in value 4.

In Excel speak, as you may know, this is called a nested if statement. Let’s have a look:

Worked Example

In our video we’ve been looking at logistics data and in the last lesson we categorised container types using a conditional column: a 20FT container is Small and any other size is called Standard. So let’s expand this example a touch:

Let’s say that instead of categorising all 3 remaining container sizes (40FT, 40HC and 45FT) as Standard, we decide to categorise 40FT and 40HC only as Standard. 45FT would then be categorised as Large.

So this is a slightly more complex conditional column than we had in the last lesson but in PowerQuery, it really doesn’t take much to add in the additional information that we need. How do we do it?

How do we add additional logical statements into our conditional column?

This depends on whether you have a conditional column already that you want to amend. As we’re amending a pre-existing step, simply go to the step that you wish to amend in the Query Settings pane and click on the cog in the step list. (Of course, if you’re not amending a pre-existing step then go to Add Column and then Add Additional Column as instructed in the last lesson)

As we saw in the last lesson, you have to fill in various boxes to detail the various components of your logical statement, including:

the column you want to search in,
the value you want to search for,
the operator and
the values to return if your statement is true or false.

However in the prior lesson you only had a single line to fill in. To add in additional tests beneath the logical statement clause you’ve already filled in, click on the button ‘Add Clause’.

A new line appears allowing you to enter your new logical statement for testing. In the case of our example then, I would fill in 2 new rows:

1 stating that if Column ‘Type’ equals 40FT then to return the value Standard
1 stating that if Column ‘Type’ equals 40HC then to return the value Standard

My Otherwise box would then be filled in with the value ‘Large’.

Does order of the logical statements matter?

Yes. In a word.

PowerQuery always considers the first logical statement first and then once it has identified the rows where that logical statement is considered true, it removes those from any subsequent logical condition test. Again, that’s all a bit theoretical so let’s have another example.

The new conditional column that we’ve just built looks like this:

put in Value “Large”.

In our example PowerQuery will look at the first logical statement, go through all the rows, identify where column ‘Type’ equals 20FT and then put “Small” in the new conditional column. Those rows are then not considered in further logical tests. PowerQuery then moves onto the next statement and applies the logical statement to the remaining rows once those 20FT rows have been removed. Once it has identified the rows where column ‘Type’ = “40FT” then it puts “Standard” in the conditional column and those rows are also not then considered for the following logical statement. This process continues until there are no more logical statements to consider Whichever rows are left receive the leftover value “Large” in the conditional column.

Now let’s say that we want to build in the additional following logic to our previous conditional column: that all rows with the text ‘HC’ in the column ‘Type’ are classed as ‘Standard High Cubed’. Here’s one way that we could fill in the conditional column:

If Column Type = “20FT” then put in value “Small”, otherwise,
if Column Type =”40FT” then put in value “Standard”, otherwise,
if Column Type =”40HC” then put in value “Standard”, otherwise,
if Column Type contains the text “HC” then put in value “Standard High Cubed”, otherwise
put in Value “Large”.

Assuming that the column ‘Type’ only has the values 20FT, 40FT, 40HC and 45FT then this text isn’t going to do anything different to our previous conditional column where the logical statement regarding the text ‘HC’ wasn’t in place. This is because, there is only one container type that has the text “HC” in it, which is the “40HC” container type. All these rows had already been considered in the conditional statement directly specifying “40HC” marking them as Standard. They were therefore not available for any subsequent logical statements.

To ensure that our new logical statement correctly considers all relevant rows, we should switch the conditional column to look like this:

If Column Type = “20FT” then put in value “Small”, otherwise,
if Column Type =”40FT” then put in value “Standard”, otherwise,
if Column Type contains the text “HC” then put in value “Standard High Cubed”, otherwise
if Column Type =”40HC” then put in value “Standard”, otherwise,
put in Value “Large”.

This conditional column will correctly classify all rows with “HC” as “Standard High Cubed”. Additionally we should note that the penultimate line is now redundant and should be removed for the sake of performance.

How can you move about the order of the conditional statements?

Easy this one! Go to the logical statement that you wish to move and select the ellipsis (the three little dots) at the end of the statement. This gives the option to move the statement up or down above or below other statements (or alternatively delete the statement altogether)

.... and a little more!

The Formula Bar – The Formula Bar does not automatically come up in the PowerQuery Editor the first time that you enter the Editor in Excel. To bring the Formula Bar up, go to the View tab. In the Layout section of the Ribbon, tick the Formula Bar box.

Remove rows – While we remove the unnecessary top rows in the video, many system reports also have totals lines at the bottom of the reports which can cause a problem when we’re doing things like summing our data later once we’re finished in PowerQuery. We also have the option to remove a specific number of bottom rows using the Remove Rows drop down in the Home tab, as well as removing alternative rows, duplicate rows, rows with errors and blank rows.

Use First Row as Headers – In the video we promote the first row in the table into the headers row. However we can also take the Headers and put them into the first row. To do this go to the same Use First Row as Headers drop down in the Home or Transform tab and select Use Headers as First Row.

Previous Lesson

Back to Course