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.
Conclusion
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!
Gilbert,
this is great! But how can you make the banding dynamic based of on an other m query table? In DAX this has already been documented (https://www.daxpatterns.com/dynamic-segmentation/), but I would also like to see the Power Query version 🙂
Thanks for the comment, that is indeed a good question.
I think it might possibly be able to be done, but would require some thought and testing!
Hi – I have multiple conditions that need to be met for multiple categories. Here is my scenario:
We have a platform that needs to be either People, Population or Panel ready based on a set of 6 conditions.
To be people ready, all 6 conditions must be met
To be population ready conditions 1,2,4,6 must be met
To be panel ready conditions 1,2,6 must be met
How would I use the custom column to create these sets of circumstances?
Thanks
Donna
Hi there
Thanks for the question.
You could do something along these lines
if [column name] = "1" and [column name] = "2" and [column name] = "4" and [column name] = "6" then "Population Ready"
else if [column name] = "1" and [column name] = "2" and [column name] = "6" then "Panel Ready"
else if if [column name] = "1" and [column name] = "2" and [column name] = "3" and [column name] = "4" and [column name] = "6" and [column name] = "6" then "People Ready"
else "Other"
I always suggest having a clause if nothing is matched to ensure that everything is covered.
Hi, I have a similar conditional column in Power Query that I’m having some issues with. My syntax looks like this:
if [Online Flag] = “true” and [In Stock] = “true” then “Available”
else if [Online Flag] = “true” and [In Stock] = “false” then “Online No Inventory”
else if [Online Flag] = “false” and [In Stock] = “true” then “In Stock, Offline”
else “Not Stocked”
No errors are displayed, however all records populate as “Not Stocked” whether they meet the conditions or not. What am I doing wrong?
Hi Rebecca,
Can you make sure that all your data has the exact same spelling?
The reason is that Power Query is Case Sensitive.
Can you also use the TRIM feature to make sure that the text does not have any leading or trailing white space?
Hi Gilbert,
Thanks for the quick reply. I did check for spelling/case differences between the two columns and also made sure they were both of the same data type just in case. I got the conditions working just now by using the actual inventory value as a condition in place of [In Stock], which is itself a conditional column. Is there a known limitation with using one conditional column as a condition in another?
Hi Rebecca,
No worries, glad to help where I can.
I would suggest using the Text.Upper([Column Name]) just to make 100% sure the values are matching.
Other than that I have always had it matching when using conditional columns.
Thanks I will try that.
How do I make conditions that contain certain strings? For example if the City column contains Berlin, Bremen or Hamburg then Region should be “North”, if city contains Munich or Ausgburg then Region should be “South”. I tried putting all the city names in the same value but it doesnt work. In the end I want something like::
Region = If [City] contains “Berlin, Bremen, Hamburg” then “North”
else if [City] contains “Munich, Ausgburg, Wurzburg” then “South”….
Hi Fathi,
What you would need to do is to have them all done individually with the else if for each condition and then it will work.
[…] allow multiple conditions, however, you can get round this by either editing the code (here’s a blog post about that) or by setting up helper columns. If you can handle the logic in Excel, you’ll find it a […]
Thank you Gilbert, everything is clear, but I’m facing with another challenge. I’m wondering how to combine different type of multiple conditions. For example: I have a list with TV programs and I want to filter TV series broadcasted between 20:00 and 21:00. So I need to set the following conditions If ProgramColumn contatins “SERIES” and StartTimeColumn >20:00 & <21:00 then "Series at 20:00" else "Something Else". I understand the logic but I don't know how to write the formula. I hope you know the way. Thanks in advance!!!
Hi Alen, thanks for the comment!
Yes for sure this can be done.
Here is an example below.
if Text.Contains([ProgramColumn],"SERIES") and StartTimeColumn >= "20:00" and StartTimeColumn < "21:00" then "Series at 20:00" else "Something Else"Thanks a lot Gilbert! Really appreciate your help. All the best!
I’m trying to figure out a way to use an or between the ands. In this case, covering both nulls or empty spaces. I’m looking to do this in one line versus going back to trim each column. Thanks!
each if [Country] = null or Text.Trim([Country]) = “” and [Org Country] = null or Text.Trim([Org Country]) = “”
then “[NOT LISTED]”
I’m trying to conditionally replace a column if both of these columns are either null or had blank space. I would like to know if this is possible for future use of this logic with other conditions. I don’t know how to force the two OR statements simultaneously as parenthesis would in a math function or SQL.
Psuedo code replace column a with not listed,
if column a is null or blank and column b is null or blank.
I was trying the following, but it made everything that had a null [NOT LISTED]. I’m’ looking for IF (column a is X OR Y) AND (column b is X OR Y) then replace column a with [NOT LISTED] Parenthesis in this language are for functions unlike SQL. Is there some character that is the equivalent or way to force the order of operations?
Table.ReplaceValue(#”Previous step”, each [Country], each if [Country”] = null or Text.Trim([Country]) = “” and [#”Org Country”] = null or Text.Trim([#”Org Country”]) = “” then “[NOT LISTED]” else null
Replacer.ReplaceValue, {“Country”} )
Hi Chris,
While it might take a few more steps I would rather break it up into individual steps. I would first check for the nulls and then once that is done then check for the blanks. Then after that look to apply the logic.
This is a great example. I needed this EXACT information, and this is by far the best site explaining it. Thanks so much! My code was much longer, but I could not have created it without your help!
if [Applicable in MAP] = “Yes” and [Future Implementation Method] = “In Scope: Use Direct”
then “ALREADY APPLICABLE”
else if [Applicable in MAP] = “Yes” and [Future Implementation Method] = “In Scope: Tailor”
then “ALREADY APPLICABLE”
else if [Applicable in MAP] = “No” and [Future Implementation Method] = “In Scope: Use Direct”
then “ADD VIA QCH”
else if [Applicable in MAP] = “No” and [Future Implementation Method] = “Out of Scope”
then “OUT OF SCOPE”
else if [Applicable in MAP] = “Yes” and [Future Implementation Method] = “Out of Scope”
then “REMOVE”
else “-“
Hi Sherry,
Thanks for your comment. There’s really awesome to hear that my blog posts help others and it helped during your situation.
For some reason I’m getting an error on the and statement when pasting in your code from above?
Hi Steven, what is the error you are getting?