Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to validate numbers between 7 and 9?

Hello,    This may be confusing but I do my best. I have a column name called a note. Each note starts with "code" followed by -, and then "number" and - then "user id". I am trying to validate the...
Need Help
Show and Tell
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    You can do this in the query editor too by adding a custom column with a formula like this (even though it has a "let" and "in" the whole thing goes in the pop up box when you add custom column.

     

    = let midpart = Text.BetweenDelimiters([Note],"-","-")
    in if Text.Length(midpart)<=9 and Text.Length(midpart)>=7 and (try Number.FromText(midpart) otherwise "error") <> "error" then "Valid" else "Invalid"

     

     

    Or you can do it in a DAX column with a formula like this

     

    Valid =
    VAR firsthyphen =
        SEARCH (
            "-",
            Validate[Note],
            ,
            0
        )
    VAR secondhyphen =
        SEARCH (
            "-",
            Validate[Note],
            firsthyphen + 1,
            0
        )
    VAR textbetween =
        MID (
            Validate[Note],
            firsthyphen + 1,
            secondhyphen - firsthyphen - 1
        )
    RETURN
        IF (
            LEN ( textbetween ) >= 7
                && LEN ( textbetween ) <= 9
                && IFERROR (
                    VALUE ( textbetween ),
                    "error"
                ) <> "error",
            "Y",
            "N"
        )
     

     

    Regards,

    Pat

     

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

You can do this in the query editor too by adding a custom column with a formula like this (even though it has a "let" and "in" the whole thing goes in the pop up box when you add custom column.

 

= let midpart = Text.BetweenDelimiters([Note],"-","-")
in if Text.Length(midpart)<=9 and Text.Length(midpart)>=7 and (try Number.FromText(midpart) otherwise "error") <> "error" then "Valid" else "Invalid"

 

 

Or you can do it in a DAX column with a formula like this

 

Valid =
VAR firsthyphen =
    SEARCH (
        "-",
        Validate[Note],
        ,
        0
    )
VAR secondhyphen =
    SEARCH (
        "-",
        Validate[Note],
        firsthyphen + 1,
        0
    )
VAR textbetween =
    MID (
        Validate[Note],
        firsthyphen + 1,
        secondhyphen - firsthyphen - 1
    )
RETURN
    IF (
        LEN ( textbetween ) >= 7
            && LEN ( textbetween ) <= 9
            && IFERROR (
                VALUE ( textbetween ),
                "error"
            ) <> "error",
        "Y",
        "N"
    )
 

 

Regards,

Pat

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi mahoneypat , 

     

    This is awesome, Thank you so much for the taking time to help me. Do you know I am getting a data type error on the second option?