Remove Leading Zero in a Text String

k_mathana , you might want to try

let
    fn = (txt as text) =>
    let
        #"First 0" = Text.PositionOf(txt, "0"),
        #"Last Number" = Text.PositionOfAny(txt, {"0".."9"}, Occurrence.Last),
        #"Dropped Leading 0" = if #"First 0" = -1 then txt else Text.Range(txt, 0, #"First 0") & Text.From(Number.From(Text.Remove(txt, {"A".."Z", "a".."z"}))) & Text.Range(txt, #"Last Number"+1)
    in
        #"Dropped Leading 0",

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLVdY0wMDAwMjVw0jVyVIrViVYy1/XxdTYwMTUzMHDUdQRKAtlOYBkjUzPdKJBiM12QHkOg+lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Unprocessed = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Processed", each Text.Combine(List.Transform(Text.Split([Unprocessed], "-"), fn), "-"))
in
    #"Added Custom"

k_mathana - I see CNENFRNL has already replied, but I had started working on this so thought I would post.

 

This looks to be a similar approach.

The function fnRemoveLeadingZeros splits the text whenever there is a transition from non-digit to "0", then trims leading zeros from the resulting substrings and joins back together.

 

let
    fnRemoveLeadingZeros =
      (string as text) =>
        let
          Split = Splitter.SplitTextByCharacterTransition ( each not List.Contains({"0".."9"},_), {"0"})(string),
          TrimZeros = List.Transform(Split, each Text.TrimStart(_,"0") ),
          Join = Text.Combine(TrimZeros)
        in Join,
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLVdY0wMDAwMjVw0jVyVIrViVYy1/XxdTYwMTUzMHDUdQRKAtlOYBkjUzPdKJBiM12QHkOg+lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Input = _t]),
    AddOutput = Table.AddColumn(Source, "Output", each fnRemoveLeadingZeros([Input]), type text )
in
    AddOutput

Regards,

Owen

Hi k_mathana 

Sure, here is a PBIX containing the queries but split up so that the function is separate from the source data.

 

• fnRemoveLeadingZeros is a function that takes a text string and removes the leading zeros as per your requirements.
• ExcelSource is a query that loads a table from an Excel file. At the moment it is pointing to an Excel file on my local drive.
• FinalTable takes ExcelSource and adds a column using fnRemoveLeadingZeros.

Actually, you could just copy the function fnRemoveLeadingZeros into Power Query in your PBIX file and use it to add a column applying this function, using Add Column > Invoke Custom Function.

 

Hopefully that helps apply the function in your scenario.

 

Regards,

Owen