Remove Leading Zero in a Text String

[k_mathana]

Hi There,

I have scenario where I need to remove leading zero in between the string, Could you please kindly look into and solve?
Current Text
25-EX000250B-2A
7-LMC045600A-A000045B
256-Z00256-00021A
Expected Result
25-EX250B-2A
7-LMC45600A-A45B
256-Z256-21A

[CNENFRNL · Accepted solution]

@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"

[OwenAuger · Accepted solution]

@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

[OwenAuger · Accepted solution]

@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

[TomMartens]

Hey @OwenAuger ,

love the inline function approach.

[k_mathana]

Dear @OwenAuger, Thanks a lot for solution given, When I try to apply this in Excel Table, I am getting some error, Could you please kindly guide me the how to change the source to excel table instead of Json Documen.

[OwenAuger · Accepted solution]

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

[k_mathana]

@OwenAuger Thank you so much for your time, appriciated. 

[CNENFRNL · Accepted solution]

@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]

Dear  @CNENFRNL,
Thanks a lot for the solution given, I was trying to apply this in excel table but I couldn't do, How would I apply this in the excel table.