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.
Try-out adding an extra step in the if clause
Else if Text.Length([email]) = 0 then null
When there are additional spaces you might need to add Text.Trim like
Text.Length(Text.Trim([email])) = 0 then null
You can replace the null with any desired output. Like "no-email"
Hope this helps
I tried your idea and that of ronrnsfld. I appreciate you taking the time to respond. 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 address) come back with 0. I tried changing to null, to a numeric, and one word TEXT. no luck. I'm open to other ideas and suggestions. Thanks again !
