Forum Discussion

alicek's avatar
alicek
Helper III
5 years ago
Solved

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

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! 

Need Help
Tips and Tricks

3 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    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.

  • HotChilli's avatar
    HotChilli
    Community Champion
    5 years ago

    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