Reply
Regular Visitor
Posts: 35
Registered: ‎02-28-2018
Accepted Solution

Column to identify if text string in correct format

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 Smiley Happy

 

OF


Accepted Solutions
Regular Visitor
Posts: 35
Registered: ‎02-28-2018

Re: Column to identify if text string in correct format

[ Edited ]

Ok it's not pretty but I think I got it Smiley Happy

 

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")

View solution in original post


All Replies
Regular Visitor
Posts: 35
Registered: ‎02-28-2018

Re: Column to identify if text string in correct format

[ Edited ]

Ok it's not pretty but I think I got it Smiley Happy

 

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")
Highlighted
CR Member
Member
Posts: 53
Registered: ‎04-20-2017

Re: Column to identify if text string in correct format

Hi @OzzieFrog

 

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"

1.png

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