Add zero to any 7 digit Number beginning with 1 or 2 only

[Tcowley44]

Hi all,

I am attempting to isolate PO#'s from a field of 9 separate columns. All PO#'s are supposed to be 8 character exactly. I removed all special characters, delaminated the fields by a space, then removed any string that was not 8 characters long.  I also removed duplicates. I then re-merged the columns with a space and re delaminated what was left. I was able to find about 95% of the PO#'s by doing this. 

My problem is we have some PO#'s that are numeric only and begin with a zero. That zero is getting dropped on occasion and I am left with a 7 digit number instead of 8. Those PO#'s are being removed and I don't want them to be.  

My thinking was to try and add a zero to any seven digit number beginning with a 1 or 2 only. That would be the 2nd digit on our numeric only PO#'s.  I would like to add this step before I remove everything that isn't 8 characters long. I do not want to add a zero to anything else and have not been able to figure out how to go about this.  Ideally I wouldn't want to add a new column to go about this to reduce steps. 

Any Ideas on how to accomplish this? The fields below are where potential PO#s could be. This is before I remove anything that isn't 8 Characters. (Not all fields have a PO#. These are removed in later steps.)

Thanks,

[Anonymous · Accepted solution]

Hi @Tcowley44 ,

Try the following formula

= if Text.Length([Column1])=7 and (Text.StartsWith([Column1],"1") or  Text.StartsWith([Column1],"2")) then Text.PadStart([Column1],8,"0") else null

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[smpa01]

@Tcowley44  explicitly in #ChangedType step make sure all columns (the ones with PO#) are of datatype string. Then do everything else and make sure any subsequent inherits the same data type.

[Daryl-Lynch-Bzy]

Hi @Tcowley44 , you could consider using the Text.PadStart function to add the leading zeros.  Try this in Power Query advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlGK1YlWMjK2NLNUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Example", each Text.PadStart( [Column1] , 8 , "0" ))
in
    #"Added Custom"

[Tcowley44]

I originally tried padstart.  My problem is it adds zero's to everything that isn't 8 characters which defeats my next steps of removing anything that isn't 8 characters. I am only tring to add a zero to 7 digit numbers that start with 1 or 2.  Everhting else I want to remove. 

Thanks,

[Daryl-Lynch-Bzy]

Could the pad function be supplemented with if statement.  Something like, if Text.Length ( _  )  = 7 and List.Contains ( { "1", "2" } , Text.Start( _ , 1 ) ) then Text.Pad else null?

[Tcowley44]

I think yes, but I am unable to figure out the statement. Every time I try, I get an Error.