Remove Leading Zeros from Strings using Power Query M

You have a string like '000123456' and you want to remove the zeros and make it '123456'

me@jaykilleen.com wrote this over 1 year ago and it was last updated over 1 year ago.


← Back to the Posts

This one is pretty simple so I'll try to provide a few examples.

Type Conversion

The first option is to convert the string to a number and (if you need to) convert it back to a string... dead simple. But it throws an error on the second column.

Check out my post on Removing Leading Zeros from Alphanumberic Strings using Power Query M if you need to handle alphanumeric conversions.

let  
  Source = #table(  
  type table [string = text, alphanumeric = text],   
    {   
      {"000123456", "000ABC123"}
    }  
  ),
  #"Changed Type" = Table.TransformColumnTypes(Source,{{"string", Int64.Type}}),
  #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"string", type text}})  
in  
  #"Changed Type1"

You will notice when you do it the easy way you end up with multiple lines of transformations... which is annoying. Changing its type and then changing again just means an extra line of code you have to maintain. If you do this a lot (and I do)... it gets frustrating.

So my preference, is to do it this way. This also works really well for Removing Leading Zeros from Alphanumberic Strings using Power Query M so you'll also find this solution over on that post.

let  
  Source = #table(  
  type table [string = text, alphanumeric = text],   
    {   
      {"000123456", "000ABC123"}
    }  
  ),
  #"Remove Leading Zeros" = Table.TransformColumns(Source,{{"string", each Text.TrimStart(_,"0"), type text}})
in
  #"Remove Leading Zeros"

Enjoy! :smile:

Similar