Removing Leading Zeros from Alphanumberic Strings using Power Query M

The tried and true way is to just convert from a string to a number and back to a string. Like I do over at Remove Leading Zeros from Strings using Power Query M...

Buuut when you get the one value that drops in one day that is alphanumeric... you get this nasty error and you are back to the drawing board.
It isn't handled by purely changing the type either. You have to start adding columns, removing columns, doing conditions, etc etc etc... it is nasty!

DataFormat.Error: We couldn't convert to Number.
Details:
    000ABC123

The really nice, elegant way that I like, is Table.TransformColumns(Source,{{"alphanumeric", each Text.TrimStart(_,"0"), type text}}).
What this does is transforms the column 'in place' (which just means you don't have to add a column and then remove the old column etc etc).
The secret sauce of this is each Text.TrimStart(_,"0") where for each row in the column each it passes in the value of the current row _ (which is depicted as an underscore) and then does a trim but ... trim as a default is set to remove a whitespace character from the start and end.

In this case, we only want to remove it from the start... so we use Text.TrimStart. You can also say what the character should be, instead of a whitespace character, we want a 0... and whala!

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

This can ofcourse be used to remove leading 0s and trailing whitespace Text.TrimEnd (which is a common pattern seen in any data coming out of SAP S4/HANA).

Similar