Forum Discussion
Issue with basic statements not evaluating correctly, Intermittently
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.
Community ChampionBefore 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.
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