‘or’ and ‘and’ in Conditional Columns

Private: PowerQuery for Finance Teams ‘or’ and ‘and’ in Conditional Columns

'or' and 'and' in Conditional Columns

Previous Session Summary

In the previous lesson, we covered how to enter the PowerQuery editor for the first time by highting some Purchase Order Data in our current workbook and then going to the Data tab and then going to From Table/Range.

We then looked at the different components of the PowerQuery editor being the Preview Screen, the Query Pane, the Tabs and Ribbon and the Query Settings Pane.

Lesson Synopsis

In this lesson we’re looking at how to tweak the M code for our conditional statement to:

– add an alternative logical test to get a single result i.e. if value ‘w’ equals ‘x’ or equals ‘y’ then ‘z’

– add an additional logical test to get a single result i.e. if value ‘v’ equals ‘w’ and value ‘x’ equals ‘y’ then ‘z’

In standard Excel, in our IF statements, we can use the OR function and the AND function to add in alternative or logical tests and we can do the same thing in PowerQuery in our conditional column, using the words ‘or’ and ‘and’.

Currently you cannot add in ‘or’ or ‘and’ using the Add Conditional Column function on the ribbon and therefore you have to go into the formula bar or the Advanced Editor to make these changes.

Resource Listing

The Video

Play Video

Lesson Notes

The OR and AND functions are old friends to those people who routinely use IF statements in Excel and they perform the following functions:

In Excel, the OR function allows you to provide an alternative logical statement when you want a single value to be populated for more than one alternative logical statements from an IF function. We can do the same thing in PowerQuery so let’s have an example of how that works,

First let’s look at the conditional column we created in the last lesson and gere’s the plain English for the conditional column:

If column ‘Type’ = “20FT” then “Small” else
if column ‘Type’ = “40FT” then “Standard” else
if column ‘Type’ = “40HC” then “Standard” else
“Large”

And then the M code for this conditional column:

#”Added Conditional Column” = Table.AddColumn(#”Changed Type1″, “Custom”,
each if [Type] = “20FT” then “Small” else
if [Type] = “40FT” then “Standard” else
if [Type] = “40HC” then “Standard” else
“Large”,
type text)

In both the plain English and the M code, the text is unnecessarily long as there are two logical statements that both return the word “Standard”. Really we want to say if the column ‘Type’ is either 40FT or 40HC then return the word “Standard”. The ‘or’ function can facilitate this as follows:

#”Added Conditional Column” = Table.AddColumn(#”Changed Type1″, “Custom”,
each if [Type] = “20FT” then “Small” else
if ([Type] = “40FT” or [Type] = “40HC”) then “Standard” else
“Large”,
type text)

You can see then that we have removed the row specifying what happens when the logical statement encounters the word ‘40HC’ and have moved that part of that logical statement into the previous line along with ‘40FT’.

The plain English for what we have done is: if the column Type is 40ft or 40HC then return the word Standard.

The 'and' word

In standard Excel, the AND function can be used in an IF statement and allows you to give more than one logical test for a single value to be returned in an IF function. Surprise, surprise, you can also do the same thing in PowerQuery.

Let’s progress our code from above and say that we want two logical tests to be met to put the value ‘Small’ in our conditional column.

Now, in our code currently, we only need to have the value ‘20FT’ in column Type to return the value ‘Small’. However let’s say that for ‘Small’ to be returned the value ‘20FT’ needs to be in column ‘Type’ and in the column Quantity there needs to be a value smaller than 15. To do this, we just need to adjust our code a fraction. Here we go:

#”Added Conditional Column” = Table.AddColumn(#”Changed Type1″, “Custom”,
each if ([Type] = “20FT” and [Quantity] < 15) then “Small” else
if ([Type] = “40FT” or [Type] = “40HC”) then “Standard” else
“Large”,
type text)

As you can see in the bold italicised section, to add in the second logical condition, we simply need to add the word ‘and’ in after the first logical test; [Type] = “20FT”. After that, we just put in the second logical test ‘[Quantity] < 15’. This text asks PowerQuery to go into the field called ‘Quantity’ and look for any rows that have a number below 15. After we have those logical tests together then we can put in the value that we wanted returned. In this case, that is the value ‘Small’.

The side effect of 'and'

The side effect of the code above is that any container of Type 20FT that does not have a value below 15 in the column Quantity will now be marked as ‘Large’. This is because we have only given PowerQuery instructions on what to do if it comes across a row of Type 20FT AND Quantity of less than 15. Otherwise we haven’t mentioned 20FT in the code at all and according to the previous code, any container type that we don’t mention specifically, must be classified as ‘Large’. Considering we previously had coded 20FT containers as ‘Small’, that seems a bit odd. We can easily resolve this issue though by typing ‘or [Type] = “20FT”’ within the brackets containing the text ‘[Type] = “40FT” or [Type] = “40HC”‘ . Power BI will then recognise this as what to do if we have a 20FT container that carries 15 units or more and designates it as ‘Standard’.

.... and a little more!

Adding in text or number values into M code – When adding a text value into M code then ensure that that the text value is surrounded by speechmarks i.e “20FT”.

When adding in a number only value into M code then the speechmarks aren’t required i.e. 20 .

Previous Lesson

Back to Course