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 order and I want to find out who not doing correct [Order is - code(3 characters)- Number (between 7 and 9, only number no characters)- userID]
 
Can anybody please tell me how to validate my number part? I want to make sure they all have numbers and digits between 7 and 9.  I just want to create a simple If statement, if my two conditions met then "Good" else "Wrong". (Note- I can split this column to get only the number part) 
 
I want something like below, and I am not sure how to get the number count and check all number. 
 

 

Flag= IF(VALUES(CCID_Data[conflict_notes_sk]) > 7 && CCID_Data[conflict_notes_sk] < 9 && CCID_Data[conflict_notes_sk] = all numbers, "Good", "Wrong")

 

My sample data ( Ex. you can see 1st row, middle (number part) has 9 number and all are number. Which is Good. 

 

Hopefully, this makes sense. Please let me know. 
Thank you 
 
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

     

4 Replies

  • 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? 

       

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

    You must have values that don't hany any hyphens and the SEARCH is returning 0.  MID throws an error when the 2nd term/start position is 0.  You will need to update the logic to avoid that scenario.

    Regards,

    Pat

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi mahoneypat , Thank you so much. I appreciate your help.