New Conditional Column from Two Other Columns in M

[UnearthlyFalcon]

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!

[jgeddes · Accepted solution]

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)

[UnearthlyFalcon]

@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".

[jgeddes]

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?

[UnearthlyFalcon]

@jgeddes Yes, I removed the old column and added the new "conditional column" to the graph. I also inserted a new table visual to verify and the "adopted" is not there, it is "dog". I'm not sure what is wrong. In my data, the pet type is another conditional column, but it is earlier in the applied steps. Is this throwing it off?

[jgeddes]

An earlier conditional step should not matter as long as the conditional column we created references the immediate previous step. 
If the "Conditional Column" is loaded into the report, and you can view the correct data in the table view, I cannot explain why you are getting the result you are. 
 I will share my pbix file to see if you can compare between and find the issue.

[UnearthlyFalcon]

I needed to change the column type to text for the original two columns, that fixed it!

[UnearthlyFalcon]

I can't see the correct data in the table view either, neither in a visual or the power querry. My data is very large though and power querry can't load enough of it to preview what I need to see. I tried to filter by "3" in the category column but it doesn't even think a 3 (or a 2 for that matter) exiss because of the data quantity. 

Regardless, I appreciate your help! My code is exactly what you provided. I'll try to keep playing around with different things to see if I can get it to work. I'll go ahead aand mark your answer as the solution, because it should be working for this type of situation haha. Thank you!