cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
k_mathana
Helper II
Helper II

Remove Leading Zero in a Text String

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

3 ACCEPTED SOLUTIONS
CNENFRNL
Super User III
Super User III

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

Screenshot 2021-03-31 094252.png

View solution in original post

OwenAuger
Super User I
Super User I

@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


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

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

 

OwenAuger_0-1617255066368.png

 


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

7 REPLIES 7
OwenAuger
Super User I
Super User I

@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


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

Hey @OwenAuger ,

 

love the inline function approach.



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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.

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

 

OwenAuger_0-1617255066368.png

 


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

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

CNENFRNL
Super User III
Super User III

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

Screenshot 2021-03-31 094252.png

View solution in original post

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors