Forum Discussion

jak8282's avatar
jak8282
Helper III
2 years ago

Not working Valid out correctly

Hi,

 

I have the following code but for some reason it is returning 54225690 as Invalid - can anyone see why - even chatgpt is saying everything looks OK?

 

Thanks

 

 

Landline Quality = 
IF(
    'Contact Details (Email & Phone)'[LandLine Number] = "",
    "Missing",
    IF(
        LEN('Contact Details (Email & Phone)'[LandLine Number]) < 5 
        || LEN('Contact Details (Email & Phone)'[LandLine Number]) > 15,
        "Invalid",
        SWITCH(
            TRUE(),
            IFERROR(FIND("*", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
            IFERROR(FIND("&", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
            IFERROR(FIND("%", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
            IFERROR(FIND("$", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
            IFERROR(FIND("£", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
            IFERROR(FIND("!", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
            IFERROR(FIND("^", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
            IFERROR(FIND("+", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
            IFERROR(FIND(";", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
            IFERROR(FIND(":", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
            IFERROR(FIND(" ", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
            IFERROR(FIND("?", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
            IFERROR(FIND("<", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
            IFERROR(FIND("-", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
            IFERROR(FIND(" ", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
            IFERROR(FIND("(", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
            IFERROR(FIND(")", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
            IFERROR(FIND("[", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
            IFERROR(FIND("]", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
            CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*1111*"), "Invalid",
            CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*2222*"), "Invalid",
            CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*3333*"), "Invalid",
            CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*4444*"), "Invalid",
            CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*5555*"), "Invalid",
            CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*6666*"), "Invalid",
            CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*7777*"), "Invalid",
            CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "0*"), "Invalid",
            CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*8888*"), "Invalid",
            CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*9999*"), "Invalid",
            CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*00000*"), "Invalid",
            CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*012345*"), "Invalid",
            CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*123456*"), "Invalid",
            CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*234567*"), "Invalid",
            CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*345678*"), "Invalid",
            CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*456789*"), "Invalid",
            "Valid"
        )
    )
)

 

 

Need Help

3 Replies

  • AndrewPF's avatar
    AndrewPF
    Helper V
    2 years ago

    It looks as if your compiler has interpreted some of your code as a comment (the green text about halfway down):

     

     

    I wonder if it has thrown something else out of kilter. 

    • jak8282's avatar
      jak8282
      Helper III
      2 years ago

      I think thats more an issue with the code inputter on the forum

  • jak8282's avatar
    jak8282
    Helper III
    2 years ago

    For some reason, making it a custom column in Power Query fixed this issue

     

    each let
    LandLine = [Landline Number],
    lenLandLine = Text.Length(LandLine),
    containsInvalidChar = List.AnyTrue(List.Transform({"*", "&", "%", "$", "£", "!", "^", "+", ";", ":", " ", "?", "<", "-", "(", ")", "[", "]"}, each Text.Contains(LandLine, _))),
    containsInvalidSequence = List.AnyTrue(List.Transform({"*1111*", "*2222*", "*3333*", "*4444*", "*5555*", "*6666*", "*7777*", "0*", "*8888*", "*9999*", "*00000*", "*012345*", "*123456*", "*234567*", "*345678*", "*456789*"}, each Text.Contains(LandLine, _)))
in
    if LandLine = "" then "Missing"
    else if lenLandLine < 5 or lenLandLine > 15 or containsInvalidChar or containsInvalidSequence then "Invalid"
    else "Valid")