Forum Discussion
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...
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"
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