ID and Company Registration Validation

[MarindaSteenkam]

Good day

I wish to add a custom column that looks at the [TaxPayer Code], if it contains 'IN" then look in the [ID Number], if it contains 13 digits, then it is correct or If it is the [TaxPayer Code] column contains anything other than 'IN' then look in the [Registration No. Column. If it agrees to the following set of criteria, the correct, or else incorrect.

Here is a screenshot of the different codes and the corresponding format.s

The code for the Individual(IN) works correctly:

// Validate ID Number for Individuals
Custom2 = Table.AddColumn(Custom1, "ID Number Check", each
if [TaxPayer Code] = "IN" then
if Text.Length(Text.From([ID Number])) = 13 and Text.Select([ID Number], {"0".."9"}) = [ID Number] then
"Correct"
else
"Incorrect"
else
"Skip")

The code for everything else does not seem to work, even if it does contain the correct format, it does not return 'Correct':

// Validate Registration No for general TaxPayer Codes
Custom1 = Table.AddColumn(ExpandedTables, "Registration No Check", each
if [TaxPayer Code] <> "IN" then
if Text.Length([Registration No]) = 14 and
Text.Middle([Registration No], 5, 1) = "/" and
Text.Middle([Registration No], 12, 1) = "/" and
Text.Length(Text.Select(Text.Start([Registration No], 4), {"0".."9"})) = 4 and
Text.Length(Text.Select(Text.Middle([Registration No], 6, 6), {"0".."9"})) = 6 and
Text.Length(Text.Select(Text.End([Registration No], 2), {"0".."9"})) = 2 then
"Correct"
else
"Incorrect"
else
"Skip")

Your assistance would be most appreciated

Kind regards

Marinda

[MarindaSteenkam]

Thank you @HotChilli  

Thank you for your assistance.

Please find below the sample data as requested. The database currently has issues where registration numbers can sometimes be captured in the ID number field and vice versa.

As per your recommendation, I have removed the conditional statements and tested the code step by step. The code correctly recognises entries with 14 characters, but it does not seem to recognise the '/' character.

For Phase 2, we would like to enhance the validation process. As shown in my previous screenshot, certain registration number formats end with specific numbers. For example:

• Close Corporation (CC) ends with /23
• Partnerships (PS) ends with /20
• Non-Profit Corporation (NPC) ends with /08

An exception is required when the Taxpayer Code is CO (Corporation), where the last digits can be either /06 or /07, both of which should be acceptable. If you can guide me on how to include this additional step, it would be most appreciated. 

Here is the sample data:

Client Name	TaxPayer Code	ID Number	Registration Number
lpha Industries	CO		2023/123456/07
Beta Partnerships	PS		2021/654321/20
Gamma Non-Profit	NPC		2020/654987/08
Delta Trust	TR		1234/2022
Epsilon Estate	EL		5678/2019
Zeta Close Corp	CC	2019/456789/23
Eta Corporation	CO		2018/567890/24
Theta Partnership	CPL		2017/678901/22
Iota S Corporation	SCO		2016/789012/24
Kappa Individual	IN	8901234567890

[HotChilli]

We'll need a data sample if you want anyone to test and debug this. It's very hard to do that just looking at the code.

-

If you want to do it yourself, copy the code into notepad as a back up and then remove all the 'if' tests except one and see if that returns 'correct', then reintroduce another test and see if it breaks. When it breaks you have found the problem.

Repeat this until you have all the tests you want working.