PQ problem with null value validation

Hi all

I do a lot of validation, for instance checking if 2 columns are equal, and if not I list the differences in a conditional column. So lets say I have a table like below
nullcheck0.png


If Column1 and Column2 are not equal, I want to show content of Column1, Column2 and Column3 in a new Check column. I do that with this code for the Check column:

Please Login or Register  to view this content.

So this would be my expected result in that column:
nullcheck1.png

Problem is that if one of the columns I want to show content from contains a null, then it does not show anything as it all evaluates to null, see first row :
nullcheck2.png

How can I get the differences listed regardless of whether some of the columns contains a null?

Bonus question: It seems PQ is not very consistant about nulls and blanks, when it comes to functions like merge etc. Some functions seems to be default null, while others are default blanks. This is also the case with different column types, like number and text. I was not aware of this, when I started using PQ, which means I now have a lot of different data, where some columns contains null, while others contain blanks. I realize that in some cases you do need to differ null from blank, but what are the general best practice about this - is it something like always replacing null with blank (or vice versa) after doing your steps, so data becomes consistant or is there another way of handling this?

Best regards
Imbizile

A simple solution is to select all the columns which could contain a null, then use the replace tool to replace null with a blank space (the value to replace is literally the word null)

This appears to already have happened in the second entry in Column 3 in your screenshot, so doing the above will give your data consistency.

Hi again

I often put the column name in front of the result, like

Please Login or Register  to view this content.

But adding that to the last solution results in the same issue, where it totally skips the nulls. So with the below code, I would have expected it to return "1:Blue || 2:Green || 3:" in the first row in the Check column, but it only returns "1:Blue || 2:Green". Is there a way to include that in this solution?

Please Login or Register  to view this content.

nullcheck3.png

Best regards
Imbizile

Thanks again for the suggestions.

The dynamic solution is very nice indeed and I think I can use that in other scenarios, but I often need to combine text from several specific columns, while leaving others out. The optimal solution would be to be able to point out specific columns, lets say i did not want Column2 to be included, so I only wanted Column1 and Column3.

But I can work with the solution mentioned, using the if statement inline.

Thanks again everyone