Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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")
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
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")
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
96 | |
79 | |
66 | |
62 |
User | Count |
---|---|
145 | |
113 | |
105 | |
85 | |
65 |