ValidISIN function
This article describes the M function syntax and usage of the pqValidISIN M Power Query function in Microsoft Excel.
Description
Returns a boolean result (True or False), indicating whether or not a given String passes the International Securities Identification Number (ISIN) code pattern and Luhn algorithm.
NOTE: This function does not validate the country prefix against the list of ISO recognised countries – nor does it provide any further validation of the codes authenticity.
Syntax
pqValidISIN([INPUT])
The pqValidISIN function has the following arguments:
- INPUT: Required. The International Securities Identification Number that you want to validate.
Remarks
- Returns True if strISIN passes the validation check, otherwise returns False.
Example
Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. Convert the range to a table. If you need to, you can adjust the column widths to see all the data.
| Company | ISIN |
|---|---|
| NEXT PLC | GB0032089863 |
| ERROR CHECKNUM | GB00B09LQS3X |
| ABBEY PLC | IE0000020408 |
| BARCLAYS PLC | GB0031348658 |
| DEBENHAMS | GB00B126KH97 |
| ERROR COUNTRY | G900B126KH97 |
Using Power Query, create a new query that is linked to the table (From Table). Add a new custom column to the query table and call it ‘Check’. Implement the following custom column formula:
=pqValidISIN([ISIN])
Close and load the query to see the results in an Excel worksheet.
Installation
Copy the M code below to a BLANK query in Power Query.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 |
let VALIDISIN = (INPUT as text) as logical => let ISIN = Text.Upper(INPUT), AlphaNums = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ", // Check the length of the ISIN LenOK = Text.Length(ISIN) = 12, // Check country prefix CountryOK = if LenOK <> false then Text.Contains(Text.Range(AlphaNums,10), Text.At(ISIN,0)) and Text.Contains(Text.Range(AlphaNums,10), Text.At(ISIN,1)) else false, // Get the check number CheckNum = if CountryOK <> false then if Text.Contains(Text.Range(AlphaNums,0,9),Text.End(ISIN,1)) then Number.FromText(Text.End(ISIN,1)) else false else false, // Construct staging table to house ISIN characters (excluding check number) Staging = if CheckNum <> false then Table.ExpandListColumn(Table.AddColumn(Table.FromValue(Text.Start(ISIN,11)), "SplitText", each Text.ToList([Value])), "SplitText") else false, // Convert alphs to digital values AlphToNum = if Staging <> false then Table.AddColumn(Staging, "AlphToNum", each try Number.From([SplitText]) otherwise Character.ToNumber([SplitText])-55) else false, // Split the numbers and transfer to number SplitNums = if AlphToNum <> false then Table.ExpandListColumn(Table.AddColumn(AlphToNum, "SplitNums", each List.Transform(Text.ToList(Text.From([AlphToNum])), each Number.From(_))),"SplitNums") else false, // ReJoin Number Characters NumString = if SplitNums <> false then Text.Combine(Table.TransformColumnTypes(SplitNums,{{"SplitNums", type text}})[SplitNums]) else false, // Get the length of the Number String NumLen = Text.Length(NumString), // Split into 1 len items again SplitString = if NumString <> false then Table.ExpandListColumn(Table.AddColumn(Table.FromValue(NumString), "SplitString", each Text.ToList(NumString)), "SplitString") else false, // Add Index column IndexNums = if SplitString <> false then Table.AddIndexColumn(SplitString, "Index", 0, 1) else false, // Get the number results after multiplying every other number starting from the right by 2 NumResults = if IndexNums <> false then Table.AddColumn(IndexNums,"NumResult", each if Number.IsEven(NumLen) then if Number.IsOdd([Index]) then Number.FromText([SplitString])*2 else Number.FromText([SplitString]) else if Number.IsEven([Index]) then Number.FromText([SplitString])*2 else Number.FromText([SplitString])) else false, // Rejoin the number characters, for the last time NumStringFinal = if NumResults <> false then Text.Combine(Table.TransformColumnTypes(NumResults,{{"NumResult", type text}})[NumResult]) else false, // Split 1 len items for the last time SplitStringFinal = if NumStringFinal <> false then Table.TransformColumnTypes(Table.ExpandListColumn(Table.AddColumn(Table.FromValue(NumStringFinal), "SplitStringFinal", each Text.ToList(NumStringFinal)), "SplitStringFinal"),{{"SplitStringFinal", type number}}) else false, // Sum the 1 len values SumNums = if SplitStringFinal <> false then List.Sum(SplitStringFinal[SplitStringFinal]) else false, // Get Luhn Algorithm result Mod10 = if NumResults <> false then Number.Mod(10 - Number.Mod(SumNums,10),10) else false, // Check the result against the check number IsCheckNum = if Mod10 <> false then Mod10 = CheckNum else false in IsCheckNum in VALIDISIN |
Pingback: Convert VBA function into Power Query - help needed