Forum Discussion
Assistance Needed for Creating a New Column
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.
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!!
1 Reply
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!!