How to validate numbers between 7 and 9?

[Anonymous]

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 

[mahoneypat]

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]

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

[mahoneypat · Accepted solution]

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]

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?