Forum Discussion
PQ M-code not recognizing empty cell in email field
Because of the way null behaves in performing logical operations, one solution is to re-arrange your statement so that you are checking for the null first.
#"Added Custom" = Table.AddColumn(#"Changed Type", "SalesChannel", each if (Text.Trim([email]) = "" or [email] = null) and [StoreName] = "Corporate" then "In-Store" else if Text.Contains([email], "amazon") then "Amazon" else if Text.Contains([email], "ebay") then "eBay" else if [StoreName] = "Event Store Server" then "Event Store" else if (Text.Trim([email]) = "" or [email] = null) and [StoreName] = "Corporate" then "In-Store" else "Co Web" )The problem is that a statement like:
Text.Contains([email], "amazon")will return null and not a logical if [email] contains null. By checking for null first in your if statement, you bypass that problem.
Because of the way null behaves in performing logical operations, one solution is to re-arrange your statement so that you are checking for the null first.
#"Added Custom" = Table.AddColumn(#"Changed Type", "SalesChannel", each
if (Text.Trim([email]) = "" or [email] = null) and [StoreName] = "Corporate" then "In-Store"
else if Text.Contains([email], "amazon") then "Amazon"
else if Text.Contains([email], "ebay") then "eBay"
else if [StoreName] = "Event Store Server" then "Event Store"
else if (Text.Trim([email]) = "" or [email] = null) and [StoreName] = "Corporate" then "In-Store"
else "Co Web"
)
The problem is that a statement like:
Text.Contains([email], "amazon")
will return null and not a logical if [email] contains null. By checking for null first in your if statement, you bypass that problem.
ronrnsfld I appreciate the response, and the insight on the sequencing. Unfortunately the same error occurs for the records with empty email content. I went back to Excel raw data table and verified again they were truly empty len(cell) come back with 0. I also tried ChielFaber solution and had no success event attempting to use null, a numeric, or any TEXT. no luck. Open to other ideas and suggestions. Thanks again !
When I made up data, the solution I proposed worked. So without a data sample that replicates the problem, I will be unable to assist further. If you can provide same, I will be happy to try again.
ronrsnfld. Thank you for following up. I elected to add a couple steps to repalce the null values with "FALSE" in email and a field titled Refernce it adds clutter to the query however I achieve the results I need in the SalesChannel field. Thank you again for the support!