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 digit should be letter

I need your help- Eg:SIKKIMCATG, AXXXX1234X

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"

     




4 Replies

  • 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

     

  • Fowmy's avatar
    Fowmy
    Super User
    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"

     




  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    You can also try this (copy whole query and replace blank query)

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvb09vb0dXYMcVeK1YlWcowAAkMjY5MIONcwAoULkrVUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Ad_PAN_check = Table.AddColumn(Source, "PAN check", each
		[
            v_letterList = Text.ToList(Text.Start([Column1], 5) & Text.End([Column1], 1)),
            v_numberList = Text.Middle([Column1], 5, 4),
            v_lengthCheck = if Text.Length([Column1]) = 10 then true else false,
            v_numberCheck = if (try Number.From(v_numberList) otherwise null) <> null then true else false,
            v_letterCheck = 
                List.Accumulate(
                    List.Buffer(v_letterList),
                    {true},
                    (s,c)=> s & {List.Contains({"a".."z"}, c, Comparer.OrdinalIgnoreCase)}
                ),
            v_AllCheck = if List.AllTrue(List.Combine({{v_lengthCheck}, {v_numberCheck}, v_letterCheck})) then "YES" else "NO"
        ][v_AllCheck], type text)
in
    Ad_PAN_check