Forum Discussion
Validate & Limit Numbers and Characters using Measure
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.
- The Alphanumeric Field accepts values only of 10 numbers and characters. No less than and more than 10.
- The Alphanumeric Field accepts values between 8-12 numbers and characters. No less than 8 and more than 12.
- The numeric field accepts only 12 numbers. No less than or more than 12 numbers.
- 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 |
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
5 Replies
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
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
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
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
