PQ M-code not recognizing empty cell in email field

[rm8168]

Hi Community,

I'm a newbie to forum so I hope I'm posting correctly. I searched forum and even tried ChatGPT for a solution.  Here is the scenario. I have large Excel table containing fields of "email", "StoreName", and adding new field of "salesChannel".  The following code was created to populate the SalesChannel based upon the content within "email" or else the "StoreName". The source data and data within PQ all Text type.

All works well except when the email field is empty.  It finds null and other email content correctly. As well it correctly finds and identifies the StoreName values.   Only when it comes across an empty email address it populates the SalesChannel with ERROR. 

I'm hoping that someone in the community can help me figure out a solution.  Thanks in advance.

#"Added Custom" = Table.AddColumn(#"Changed Type", "SalesChannel", each
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"
)
in
#"Added Custom"

==============

Operating Office 365 64bit

Regards

RM8168

[ronrsnfld · Accepted solution]

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.

[rm8168]

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 !

[ronrsnfld]

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.

[rm8168]

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!

[ChielFaber]

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

[rm8168]

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 !