Forum Discussion

kalyanittayacs's avatar
kalyanittayacs
New Member
2 years ago
Solved

Logic to get Valid PAN number

Hello Team,   I have to verify valid pan (YES or NO) based on few condition- 1) length of pan should be 5 digit 2)first 5 digits should be letters 3) next 4 digit should be number 4)last 1 digi...
Need Help
  • Fowmy's avatar
    Fowmy
    2 years ago

    kalyanittayacs 

    Please find attached the file.
    You need to add a custom column. Assume you have the data in a column called PAN.

     

    if
        let
            __p = Text.Upper([PAN])

        in
            Text.Length(__p) = 10 and 
            List.AllTrue(List.Transform(Text.ToList(Text.Start(__p , 5)), each List.Contains({"A".."Z"}, _ ) )) and 
            List.AllTrue(List.Transform(Text.ToList(Text.Middle(__p , 5, 4)), each List.Contains({"0".."9"}, _ ) )) and 
            List.Contains( {"A".."Z"}, Text.End(__p , 1)) 
    then
        "Yes"
    else
     "No"

     




PijushRoy's avatar
PijushRoy
Community Champion
2 years ago

Hi kalyanittayacs 

Can you try below DAX code in calculated column

Column = VAR _len = LEN('Table'[PAN])
VAR _1stLetter = LEFT('Table'[PAN],1)
VAR _2stLetter = MID('Table'[PAN],2,1)
VAR _3stLetter = MID('Table'[PAN],3,1)
VAR _4stLetter = MID('Table'[PAN],4,1)
VAR _5stLetter = MID('Table'[PAN],5,1)
VAR _6stLetter = MID('Table'[PAN],6,1)
VAR _7stLetter = MID('Table'[PAN],7,1)
VAR _8stLetter = MID('Table'[PAN],8,1)
VAR _9stLetter = MID('Table'[PAN],9,1)
VAR _10stLetter = MID('Table'[PAN],10,1)
RETURN
IF(
    _len = 10 && 
    _1stLetter IN {"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"} &&
    _2stLetter IN {"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"} &&
    _3stLetter IN {"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"} &&
    _4stLetter IN {"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"} &&
    _5stLetter IN {"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"} &&
    _10stLetter IN {"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"} && 
    _6stLetter IN {"1","2","3","4","5","6","7","8","9","0"} &&
    _7stLetter IN {"1","2","3","4","5","6","7","8","9","0"} &&
    _8stLetter IN {"1","2","3","4","5","6","7","8","9","0"} &&
    _9stLetter IN {"1","2","3","4","5","6","7","8","9","0"}, "Thre","False")


Length should be 10 Digit

If solve your requirement, please mark as SOLUTION

Thanks
Pijush