Why does Conditional Column logic throw errors?

[alicek]

Hello all, 

I have two columns that I am trying to use the "Add conditional column" function in the Query Editor to combine into one based on a series of IF statements. 

The columns are on the left, and based on my logic (put in beneath this table) they should make the third column on the right:

ProjectStage	CSA Project Stage	FINAL (They should make...)
Completed: detailsdetails		Complete
null	Canceled	Cancelled
In Progress: Blahlblah		In Progress
Completed: differetndetails		Complete
In Progress: differentblalha		In Progress
null	Complete	Complete
Cancelled		Cancelled
null	In Progress	In Progress

The logic I've tried to use is:

So:
1. If CSA Project Stage has "Canceled", fill in "Cancelled"

2. If CSA Project Stage is not null, fill in the value from CSA Project Stage
3. If ProjectStage contains "Progress", fill in "In Progress"
4. If ProjectStage contains "Completed", fill in "Complete"

5. Otherwise, fill in the value in ProjectStage

HOWEVER, the results throw an "Error" in the FINAL column for every cell from ProjectStage.

If I reverse the logic, it throws an error in every cell from CSA Project Stage.

Where is my logic going wrong here? Thank you!

Ps I also tried re-doing it in a different way that had equals null instead of does not equal null. See below. It also did not work and threw Error in for everything except the first three clauses.

This is the Advanced Editor M code (that shows it correctly understands null means null and not "null" as text:
#"Final_ProjectStage - Added Cond Col" = Table.AddColumn(#"Reordered Columns1", "Final_ProjectStage", each if Text.Contains([CSAPProjectStage], "Cancel") then "Cancelled" else if Text.Contains([ProjectStage], "Progress") then "In Progress" else if Text.Contains([ProjectStage], "Complete") then "Complete" else if [CSAPProjectStage] = null then [ProjectStage] else [CSAPProjectStage]),

ANOTHER UPDATE:
I value replaced the "null"s in CSA Project Stage to say "BLANK" in text. I also re-ordered the clauses. What is confusing is that I don't even address the nulls, and it still throws errors where CSA Project Stage is Canceled. 

ther eis something going wrong here that does not have to do with "null"s. Does anyone have any ideas?

[v-jiewu-msft · Accepted solution]

Hi @alicek ,

Based on my testing, please try the following methods:

1.Create the sample table.

2.Select Add conditional Column and add the clause.

3.The result is shown below.

You can also view the following documents to learn more information.

Add a conditional column - Power Query | Microsoft Learn

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vanessafvg]

can you click on the  error link and see what the message says?

Most likely its because you have null values in project stage columns, so create another line in the  conditional statement after the last condition that says if the column name is null then set it to null null or Unknown or whatever value it is you need to understand that its empty.   But you need to handle the null value.  

[alicek]

Hi @vanessafvg ! Thanks for your response! I tried adding another clause for handling if ProjectStage equals null, but it did not change anything. Thoughts?

[ExcelMonke]

Your word "null" and the value of null are unfortunately not the same thing. If your original column is a calculated column, consider changing how it derives the null value. 

Alternatively, there could an option for blanks in your conditional column? 

[alicek]

Thanks for your response @ExcelMonke! The M in the Advanced Editor implise it does recognize null as true null, not as text null (see screenshot I added at the end below). If it read it as null, wouldn't it just not trigger the if statement, instead of making the field an Error?