Forum Discussion

v2's avatar
v2
Frequent Visitor
5 years ago
Solved

Power Query add a leading Zero for variable length field

Hi, I have date field that could have a lenght of 5 characters or 6 character as below:   11220 (read as 1/12/20) 121220 (read as 12/12/20)   How do i format this to be 6 characters. so if its ...
Need Help
  • v2's avatar
    v2
    5 years ago

    Thanks for your solution. I was unable to open your file as i have an older version of powerBi on my work computer, but used your solution and modified it to the below solution and it seems to work. Thanks.

     

    = Table.AddColumn(#"Kept Last Rows", "DATES_FIXED", each if Text.Length([DATE]) = 6 then ([DATE]) else "0" & [DATE])

amitchandak's avatar
amitchandak
Super User
5 years ago

v2 , Try like

Text.PadStart([Date], 6,"0")

  • v2's avatar
    v2
    Frequent Visitor
    5 years ago

    Thanks for your solution AmitChandak.