Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rdvasisht
Frequent Visitor

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.

 

 

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

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"

 

 

extractnums.png

 

regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

1 REPLY 1
PhilipTreacy
Super User
Super User

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"

 

 

extractnums.png

 

regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.