getting length of a alphanumeric column

[rdvasisht]

Hi there,

 i have Zip code column in my data set. lots of entries for zip code are incorrect such as:

N2E--s,

SDS23R,

UNKOWN, etc.

i am creating an audit dashboard and i am struggling to calculate length of numeric and non-numeric values in each cell of the the column. for e.g.

Zip code	length of numeric values	length of no-numeric values
N2G9F9	3	3
L4F9K4	3	3
I8E9T9	3	3
T9R 997	4	2
unkown	0	6
424 412	6	0

The above table is what i am looking for. i will filter the length columns for 3 to get the almost accurate percent of zip code entries.

any help is appreciated.

[PhilipTreacy · Accepted solution]

Hi @rdvasisht 

Download example PBIX file

In Power Query this code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jNyt3SzVIrViVbyMXGz9DYBMz0tXC1DIKIhlkEKlpbmYHZpXnZ+eR6YaWJkomBiaKQUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Zip code" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Non-Numeric Values", each Text.Length(Text.Select(Text.Lower([Zip code]) , {"a".."z"}))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Numeric Values", each Text.Length(Text.Select(Text.Lower([Zip code]) , {"0".."9"}))),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Numeric Values", Int64.Type}, {"Non-Numeric Values", Int64.Type}})
in
    #"Changed Type"

regards

Phil