Issue with basic statements not evaluating correctly, Intermittently

[Anonymous]

Hi all,

2016 Excel PowerQuery Question

After finding out about PowerQuery I have worked to automate several reports I do at work, mostly successfully.  However, occasionally, I come across issues that are difficult to figure out.  Today how I have come across one that just makes no sense, to me.  Can someone, please, explain or help me to understand why Power Query is doing this?

So I have a table power queried of survey system data.  Two of the custom columns in the table, one of which concatenates 7 fields together and another that uses an if statement are evaluating incorrectly (seemingly).  The weird thing is that it "seems" to be intermittently, and I don't know why.  It's not like the whole column is erroring out, some of the fields are being generated correctly, and others are not.

The formulas (M-code) is as follows:

ResponsesConcat (Used to concatenate the responses to several questions together into one field):

= Table.AddColumn(#"Added Custom3", "ResponsesConcat", each [Q1R] & [Q2R] & [Q3R] & [Q4R] & [Q5R] & [Q6R] & [Q7R])

AutoResponse1 (Used to determine if the survey is dissatisfied [if statement] then concatenate a response together):

= Table.AddColumn(#"Added Custom12", "AutoResponse1", each if [Measured Response] < 4 then "Greetings " & [First Name] & ",

I’m emailing you as follow-up to a recent survey you completed regarding your experience contacting the OD Service Desk team.  In your survey you indicated the following:
" & [ResponsesConcat] & "
" & [CommentFound] & "Would you be willing to elaborate on why you felt this way?  We are always looking to improve upon the service we provide, and your feedback would be most helpful in this pursuit!

Note: To respond, please reply to this email!" else "")

Note: The measured response < 4 is the evaluation of a survey response field ranging between 1 (Very Dissatisfied) and 5 (Very Satisfied).  Anything less than 4 requires follow-up.

So the weirdest thing about all of this, as I mentioned above, is that only some of the rows aren't evaluating correctly (in these particular columns).  On many of the rows the responses are concatenated properly, and on many rows the AutoResponses are being generated properly.

It seems however that the ones not concatenating properly are those where one of the responses was null, maybe it freezes up with it has to consider including a null field.  On the IF statement field it seems to calculate correctly for all values of 1 but doesn't generate AutoResponses for values 2 or 3, even though the IF statement clearly indicates < 4.  I could be reading too much into it, I don't know why these two factors would cause an issue with these two fields.

I look forward to hearing back from you, as this is driving me bonkers trying to figure it out.

Sincerely,

Kris

For reference:

[edhans · Accepted solution]

Before you do any custom columns, you need to make sure your other columns are typed properly. You have many set to the Any type (the 123/ABC) type, which means PowerQuery isn't enforcing any rules on them, and that can lead to unpredictable results. One row will have one set of data and the next another type. Set them to number, integer, text, whatever. There are cases where you want to leave as 123/ABC if you are doing some more complex error trapping, but that doesn't seem to be the case here.

Nulls also have specific issues to deal with. Null & "Text" will evaluate to null. Not "Text". Any math on a null is null. null + 1 = null.

You can replace null with blank, and blank behaves a bit better. Blank & "Test" evaluates to "Test", not blank, or null. To do that, select the column with nulls and do a replace in the Transform tab. Type "null" in the first box and leave the second box blank. Then do your concatenations.

Does that help? If not, please post a sample data file so we can investigate further.

[Anonymous]

Hey @edhans ,

Thank you for responding so promptly!

Your response has helped greatly, I was unaware that PowerQuery handles null values in this way, and it seems to have been the cause of all my woes.  Wherever there was a field that handled a null value or resulted in one, it was like multiplying by 0 and the remaining evaluation failed.  

Thank you!!  Thank you!

Sincerely,

Kris