Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

 

OF

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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")
  • CR's avatar
    CR
    Resolver II
    7 years ago

    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