Using Text.PadStart in Query Editor of a Custom Column to add 0 in front of single digits?

[alicek]

Hello all, 

I have a column with site IDs that are filled with: 1, 2, 3, 4, 10, 11, 12, A, AB, and B. 

Only the sites with numbers are "Construction" sites (the alphabetic site IDs are a different kind of site). 
I am creating a custom column that will re-write these IDs to match another table I need to merge onto using that column.

Then the column would be filled in with: Site 01, Site 02, Site 03, Site 04, Site 10, Site 11, Site 12, Site A, Site AB, and Site B.

I wrote this text in the Custom Column editor, but it is not adding a 0 in front of the single digit numbers:

if [Site Type] = "Construction"
then Text.Combine({"Site ", Text.PadStart(Text.From([#"Site #"], "en-US"), 2, "0")})
else Text.Combine({"Site ", Text.PadStart(Text.From([#"Site #"], "en-US"), 1)})

Can anyone point me to where I've gone wrong? Thank you!!

P.S. If I'm going in the wrong direction, and you can re-write this not to be based on Construction, but just a way that always adds a 0 to single digit numbers (not letters or double-digit numbers), then that would be great too! 

[ChrisMendoza · Accepted solution]

@alicek - I attempted as:

= Table.AddColumn(#"Added Custom", "Custom1", each Text.Combine({"Site ", if Value.Is(Value.FromText([siteID]), type number) then Text.PadStart([siteID],2,"0") else [siteID]}))

Used → https://stackoverflow.com/a/39736101

[HotChilli]

It looks alright to me.

Debug it by cutting it right down.  Try adding a column with just 

Text.PadStart(Text.From([#"Site #"], "en-US"), 2, "0")

If that works, add in the Text.Combine, then the 'if'statement

[PhilipTreacy]

Hi @alicek 

This code will work.  Here's a sample PBIX file.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PKy4pKk0uyczPU9JRMlSK1YlW8k9Ly0xOhXPR1BihqjHCpsYYVY0xNjUmqGpMsKkxNEBzkAE2VY6oihydiFCEVQ1QMBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Site Type" = _t, #"Site ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Site Type", type text}, {"Site ID", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Site Type] = "Construction" and List.ContainsAny(Text.ToList([Site ID]), { "0" .. "9" }) then Text.Combine( {"Site ",Text.PadStart([Site ID],2,"0")}) else if [Site Type] = "Construction" then Text.Combine( {"Site ", [Site ID]} ) else [Site ID])
in
    #"Added Custom"

To give you this

I wasn't sure of your table structure and column names but if you have trouble adapting this to your situation let me know and I can help further.

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.