Forum Discussion

rdvasisht's avatar
rdvasisht
Frequent Visitor
3 years ago
Solved

getting length of a alphanumeric column

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 codelength of numeric valueslength of no-numeric values

N2G9F9

33

L4F9K4

33

I8E9T9

33
T9R 99742

unkown

06

424 412

60

 

 

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.

 

 

Tips and Tricks
  • PhilipTreacy's avatar
    PhilipTreacy
    3 years ago

    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

1 Reply

  • PhilipTreacy's avatar
    PhilipTreacy
    Icon for Super User rankSuper User
    3 years ago

    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