Forum Discussion
New Conditional Column from Two Other Columns in M
Hi all,
I have a table like below.
| ID | Pet Type | Number Category |
Unique 1 | Cat | 1 |
| Unique 2 | Dog | 2 |
| Unique 3 | Cat | 3 |
| Unique 4 | Dog | 3 |
| Unique 5 | Cat | 2 |
| Unique 6 | Dog | 1 |
I would like to create a new column where any dog that is in number category 3 means they were adopted but cats just remain as cats, as do pets in the other nuber categories. Sorry, this is not a great example logically. A table below is what I would like the end result to be:
| ID | Pet Type | Number Category | New Column |
Unique 1 | Cat | 1 | Cat |
| Unique 2 | Dog | 2 | Dog |
| Unique 3 | Cat | 3 | Cat |
| Unique 4 | Dog | 3 | Adopted |
| Unique 5 | Cat | 2 | Cat |
| Unique 6 | Dog | 1 | Dog |
I would like to make this new column in M rather than Dax (I think). I have seen examples with the switch function but realize that does not work in M and I'm not sure how to nest my IF statements accordingly. I tried the below but my syntax was wrong, as the ANDs are not right because it expects THEN
#"Added Conditional Column2" = Table.AddColumn(#"Reordered Columns", "New Column", each if Text.Contains([Pet Type], "Cat") AND each if Text.Contains([Number Category], "1") then "Cat" else if Text.Contains([Pet Type], "Dog") AND each if Text.Contains([Number Category], "1") then "Dog" else if Text.Contains([Pet Type], "Cat") AND each if Text.Contains([Number Category], "2") then "Cat" else if Text.Contains([Pet Type], "Dog") AND each if Text.Contains([Number Category], "2") then "Dog" else if Text.Contains([Pet Type], "Cat") AND each if Text.Contains([Number Category], "3") then "Cat" else if Text.Contains([Pet Type], "Dog") AND each if Text.Contains([Number Category], "3") then "Adopted"
Thank you in advance!
This should solve your issue.
= Table.AddColumn(previousStep, "ConditionalColumn", each if [Number Category] = 3 and [Pet Type] = "Dog" then "Adopted" else [Pet Type], type text)
7 Replies
This should solve your issue.
= Table.AddColumn(previousStep, "ConditionalColumn", each if [Number Category] = 3 and [Pet Type] = "Dog" then "Adopted" else [Pet Type], type text)jgeddes thank you for the reply! It did not quite work. It did create a new column and replicated the text from the pet type column, however, the new "Adopted" title is not incorporated for some reason. All the orange in the below graph meets the criteria for "dog" and "3" but it still has the old tag of "dog".
If the code provided gave you a column result similar to
We can assume that the M code worked as intended.
Can you confirm that you are using the new "conditional column" in the chart you shared?
