New Column With Nested IFs

[kitcatnj]

I am analyzing a column and based on what's in there, adding a new column. There are 3 things to check for.

Does the column contain "??",  NULL, and anything not = to 2 char in length

This part works. 

-Checking for nulls and ??

= Table.AddColumn(STAGING_workorder, "siteRefGB", each if Text.Contains([siteref], "??") then "BAD" else if [siteref] = null then "BAD" else "GOOD")

Once i add the check for length to the query, it fails. 
IF(LEN ([siteref]) <> 2 then "bad" ) 

BUT... If i do a check on ONLY the length, that works. I can't combine them

Any help would be appreciated!

[kitcatnj]

THANK YOU !!!! That was it! This now works. 

= Table.AddColumn(TABLENAME "siteRefGB", each if Text.Contains([siteref], "??") then "BAD" else if [siteref] = null then "BAD" else if Text.Length([siteref]) <> 2 then "BAD" else "GOOD")

[AilleryO · Accepted solution]

Hi,

I (think you missed the M function you need 😉

LEN doesn't exist in M , but Text.Length does 🙂

more info : https://learn.microsoft.com/en-us/powerquery-m/text-length

Hope it helps