Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kalyanittayacs
New Member

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

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@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"

 

Fowmy_0-1703685035746.png


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

4 REPLIES 4
dufoq3
Super User
Super User

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

dufoq3_0-1703688440048.png

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Fowmy
Super User
Super User

@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"

 

Fowmy_0-1703685035746.png


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Thank you it is worked

PijushRoy
Super User
Super User

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors