Multiple conditions for a conditional column in Power Query
I am often working on datasets where there is more than one condition for a conditional column.
And whilst the GUI based Conditional column is really good, it currently does not have the capability for multiple conditions.
In this blog post below, I will demonstrate how to achieve this.
In my example below, I have a table that has got Bike Brands and Types.
I now want to create a rating based on both the Brand and Type.
I do this by creating a Custom Column
The way the multiple conditions work is based on the following pattern:
if [Column Name1] = “Condition” and [Column Name 2] = “Condition” then “Result”
else if [Column Name1] = “Condition2” and [Column Name 2] = “Condition2” then “Result2”
else if [Column Name1] = “Condition3” and [Column Name 2] = “Condition3” then “Result3”
else “Unknown Result”
This is shown with my working example below.
Which results in my table now having a new column called Rating which has the multiple conditions for my conditional column.
Another thing to note is that I could also do a range between two values which is essentially multiple conditions for a conditional column.
What I had to first do was to change the Amount column from Text to Whole Number, so that my conditions would work. After which I then used the following conditions
Which resulted in getting the banding that I was after.
And finally, (the last one I promise!), is where I can use the flexibility within Power Query to convert the Amount value on the fly from a Text value to a Number value for my conditional column.
This will allow me to keep my column in my table as a text value.
I hope that you have found this blog post useful and as always if you have any suggestions or comments please leave them in the section below.
Thanks for reading!