Column to identify if text string in correct format

[Anonymous]

Hi all,

I have tried several formulas to achieve what I am looking for and have only had mixed results so I was hoping someone would have a clever idea.

I have a column (Name) which contains some value I want to include/exclude in my report and the only way to identify it is to assess whether it complies with the following format: BUN-D-016902-000. Effectively 3 digits - 1 letter - 6 digits - 3 digits.

I have used search("-",.......) but it still includes data I wish to exclude. ANy thoughts?

Thanks in advance 🙂

OF

[CR]

Hi @Anonymous

You can add a custom column through your query editor with the following code:

if Text.Length([Name]) <>  16 then "NO 0"
 else if Text.Middle([Name],5,1) <> "-" then "NO 1"
 else if Text.Middle([Name],7,1) <> "-" then "NO 2"
 else if Text.Middle([Name],14,1) <> "-" then "NO 3"
 else "YES"

It will show you what is wrong and you will select the YES or the ones starting with NO for your count.

NOTE => I didn't test it so to be checked first in your PBIX !

Regards,

CR

[Anonymous · Accepted solution]

Ok it's not pretty but I think I got it 🙂

EDM/SPF Format Checker = 
iferror(IF(ISTEXT(mid('All Documents'[Name],1,1))=TRUE&&
ISTEXT(mid('All Documents'[Name],2,1))=TRUE&&
ISTEXT(mid('All Documents'[Name],3,1))=TRUE&&
SEARCH("-",'All Documents'[Name],1,0)=4&&
ISTEXT(mid('All Documents'[Name],5,1))=TRUE&&
SEARCH("-",'All Documents'[Name],5,0)=6&&
ISNUMBER(VALUE(mid('All Documents'[Name],7,1)))=TRUE&&
ISNUMBER(VALUE(mid('All Documents'[Name],8,1)))=TRUE&&
ISNUMBER(VALUE(mid('All Documents'[Name],9,1)))=TRUE&&
ISNUMBER(VALUE(mid('All Documents'[Name],10,1)))=TRUE&&
ISNUMBER(VALUE(mid('All Documents'[Name],11,1)))=TRUE&&
ISNUMBER(VALUE(mid('All Documents'[Name],12,1)))=TRUE,
"EDM/SPF Format","Incorrect Format"),"Incorrect Format")