Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
alicek
Helper III
Helper III

Why does Conditional Column logic throw errors?

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:

ProjectStageCSA Project StageFINAL (They should make...)
Completed: detailsdetails Complete
nullCanceledCancelled
In Progress: Blahlblah In Progress
Completed: differetndetails Complete
In Progress: differentblalha In Progress
nullCompleteComplete
Cancelled Cancelled
nullIn ProgressIn Progress

 

The logic I've tried to use is:

alicek_0-1715717903490.png

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.

alicek_1-1715718159067.png

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.

alicek_1-1715730429634.png

 

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]),

alicek_0-1715730320348.png
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?

alicek_0-1715731206887.png

 

1 ACCEPTED SOLUTION
v-jiewu-msft
Community Support
Community Support

Hi @alicek ,

Based on my testing, please try the following methods:

1.Create the sample table.

vjiewumsft_0-1715850843072.png

2.Select Add conditional Column and add the clause.

vjiewumsft_1-1715850857350.png

3.The result is shown below.

vjiewumsft_2-1715850864727.png

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.

View solution in original post

5 REPLIES 5
v-jiewu-msft
Community Support
Community Support

Hi @alicek ,

Based on my testing, please try the following methods:

1.Create the sample table.

vjiewumsft_0-1715850843072.png

2.Select Add conditional Column and add the clause.

vjiewumsft_1-1715850857350.png

3.The result is shown below.

vjiewumsft_2-1715850864727.png

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
Super User
Super User

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.  





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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
Solution Supplier
Solution Supplier

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? 

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?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.