Forum Discussion
Helper IIIValidate the Name Format in the Dataset
Dear Experts,
I am building a report to validate the Name Format in a data set.
The correct name format should be "First Name + Space + Last Name" (e.g. Tom Lu)
Any of following formart is invalid:
- If there is no space between First Name and Last Name, it is invalid.
- If there is space within the First Name, it is invalid.
- If there is no Last Name, it is invalid.
- If there is no value, it is invalid.
| Name | Validation Result |
| Tom Lu | Valid |
| AndyWu | Invalid |
| Mei Xin Wu | Invalid |
| Smith | Invalid |
| Invalid | |
| Jim Wong | Valid |
Is it possbiel to create a custom column to achieve it?
Many thanks!
Hi TomLU123,
Based on my test, we can take the following steps to meet your requirement.
1.Enter the data as you described and new a column using the formula.
c2 = SUBSTITUTE(Table1[Name]," ","")
2.Create another new calculate column by the formula.
Validation Result2 = IF(ISBLANK(Table1[c2]),"Invalid",IF(LEN(Table1[c2])+1=LEN(Table1[Name]),"Valid","Invalid"))
3.Then we can get the result as we excepted.
For more details, please check the pbix as attached.
Regards,
Frank
1 Reply
v-frfei-msftCommunity Support
Hi TomLU123,
Based on my test, we can take the following steps to meet your requirement.
1.Enter the data as you described and new a column using the formula.
c2 = SUBSTITUTE(Table1[Name]," ","")
2.Create another new calculate column by the formula.
Validation Result2 = IF(ISBLANK(Table1[c2]),"Invalid",IF(LEN(Table1[c2])+1=LEN(Table1[Name]),"Valid","Invalid"))
3.Then we can get the result as we excepted.
For more details, please check the pbix as attached.
Regards,
Frank