Assistance Needed for Creating a New Column

[InsightSeeker]

Hello,

I need help to create a new column based on the following conditions:

- If the settle name is equal to "Card" or "OCCF", and
- The code is equal to "000100160", and
- The number length is 11, and
- The first 4 digits of the number are "4398", and
- The last 4 digits of the number are "1614", and
- The code is not blank,

then return "valid"; otherwise, return "error".

The data is in Table1.

settle_name	Code	Number	Code
Card	000100160	4398XX7614	162932
Card	000100160	4398XXX1614	101256
OCCF	000100160	4398XXX16145	105352
OCCF	000100160	14398XXX1614	105938
OCCF	000100160	4398XXX1614	106780
Card	000100160	4398XXX1614
Card	000100160	4398XXX1614	111260
Card	000100160	4398XXX1614	111496

Result

settle_name	Code	Number	Code	Status
Card	000100160	4398XX7614	162932	Error
Card	000100160	4398XXX1614	101256	Valid
OCCF	000100160	4398XXX16145	105352	Error
OCCF	000100160	14398XXX1614	105938	Error
OCCF	000100160	4398XXX1614	106780	Valid
Card	000100160	4398XXX1614		Error
Card	000100160	4398XXX1614	111260	Valid
Cash	000100160	4398XXX1614	111496	Error

Thank you for your assistance.

[rajendraongole1 · Accepted solution]

Hi @InsightSeeker -  Here's how you can create a calculated column based on the given conditions

NewColumn =
IF (
OR (
'YourTableName'[settle name] = "Card",
'YourTableName'[settle name] = "OCCF"
) &&
'YourTableName'[code] = "000100160" &&
LEN('YourTableName'[number]) = 11 &&
LEFT('YourTableName'[number], 4) = "4398" &&
RIGHT('YourTableName'[number], 4) = "1614" &&
NOT ISBLANK('YourTableName'[code]),
"valid",
"error"
)

you can replace 'YourTableName' with the actual name of your table, and 'settle name', 'code', and 'number' with the actual column names in your dataset.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!