CODEWITHSUNDEEP
powerquery
Aaron
Aaron

Reputation: 331

Adding leading Zeros into Day and Month Value

I have a simple table that has a column with a date in this format: MM/DD/YYYY. Unfortunately, there are some folks who are working without leading zeros. Therefore I would like to add a leading zero into the Month and Day element using Power Query to have a common format. But how? Does someone have any function to share?

Upvotes: 0

Views: 2560

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60344

Again, not sure why you want to do this, but

  • Assuming all of the entries are text that looks like dates, you can use the following M-Code:

  • Split the string on the delimiter

  • Change each entry in the list to a number

  • Add 2000 to the last number

  • Change the numbers back to text with a "00" format

  • Recombine with the delimiter

let
    Source = Excel.CurrentWorkbook(){[Name="Table29"]}[Content],

//set type = Text
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TextDate", type text}}),
 
    xform = Table.TransformColumns(#"Changed Type",
        {"TextDate", each 
            let 
                x = Text.Split(_,"/"),
                y = List.Transform(x,each Number.From(_)),
                z = List.ReplaceRange(y,2,1, {2000+y{2}}),
                a= List.Transform(z,each Number.ToText(_,"00")),
                b = Text.Combine(a,"/")
            in b}) 
in
    xform

enter image description here

I am thinking a better solution might be to set up your data entry method so that all dates are entered as dates rather than text

Upvotes: 2

Related Questions