Validate & Limit Numbers and Characters using Measure

[gauravnarchal]

Dear All - I have a field in my data that I want to validate & Limit Numbers and Characters

I want to achieve the below by creating separate measures.

1. The Alphanumeric Field accepts values only of 10 numbers and characters. No less than and more than 10. 
2. The Alphanumeric Field accepts values between 8-12 numbers and characters. No less than 8 and more than 12. 
3. The numeric field accepts only 12 numbers. No less than or more than 12 numbers.
4. The Alphabets field accepts only 12 characters. No less than or more than 12 numbers.

Data - Table 1 - Column "Field"

Field

1234AWQ3

12345678

12345678901

132JHK87

876J9879

YTRGYGHJK

[PhilipTreacy · Accepted solution]

Hi @gauravnarchal 

Download this PBIX file with the following code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNnEMDzRWitWBcEzNzC1QOJYGhhC+sZGXh7eFOZhjYW7mZWlhbgnmRIYEuUe6e3h5K8XGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Field = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Result 1", each if Text.Length([Field]) = 10 

and List.ContainsAny(Text.ToList(Text.Lower([Field])), {"a..z"})

and List.ContainsAny(Text.ToList(Text.Lower([Field])), {"0..9"})

then true else false),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Result 2", each if Text.Length([Field]) >= 8 and Text.Length([Field]) <= 12 

and List.ContainsAny(Text.ToList(Text.Lower([Field])), {"a".."z"})

and List.ContainsAny(Text.ToList(Text.Lower([Field])), {"0".."9"})

then true else false),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Result 3", each if Text.Length([Field]) = 12 then

if List.ContainsAny(Text.ToList(Text.Lower([Field])), {"a".."z"})

then false else true else false),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Result 4", each if Text.Length([Field]) = 12 then

if List.ContainsAny(Text.ToList(Text.Lower([Field])), {"0".."9"})

then false else true else false)
in
    #"Added Custom3"

Regards

Phil

[PhilipTreacy]

Hi @gauravnarchal 

OK, but do you have separate columns for each of these types of inputs?  Or are the columns made up of mixed field types, that is, some fields in the column are Type 1 : Alphanumeric and 10 characters, and some fields are Type 4 : Letters only and must be 12 characters?

If so, how do you distinguish between the fields?  Power Query won't know which field is which type.

How do you want the results flagged?  A single column with all errors in it, or one error column for each different type of field?

Please suypply more complete sample data and an example of your expected result/output.

Regards

Phil

[gauravnarchal]

Hi @PhilipTreacy - I want to display the result  "Single column with all errors"

Below is the 

Field	Result 1	Result 2	Result 3	Result 4
1234AWQ3	FALSE	TRUE	FALSE	FALSE
12345678	FALSE	FALSE	FALSE	FALSE
12345678901	FALSE	FALSE	FALSE	FALSE
132JHK87	FALSE	TRUE	FALSE	FALSE
876J9879	FALSE	TRUE	FALSE	FALSE
YTRGYGHJK	FALSE	FALSE	FALSE	FALSE

[PhilipTreacy]

Hi @gauravnarchal 

You can't restrict/prevenmt data entry like this.  The user can enter whatever they want (subject to any restriuctions in the data entry point) but PBI/Power Query/DAX can only test the data after it's been entered and flag problems.  Is that what you want?

Regards

Phil

[gauravnarchal]

Hi @PhilipTreacy  - Yes, I want to flag all the incorrect data that is entered.

Thanks

Gaurav