Power Query add a leading Zero for variable length field

[v2]

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 5 characters then add a leading 0 else leave as is. And also format with a "-"

the solution must be:

01-12-20

12-12-20

thanks

[mussaenda]

How's your date if the month is October below?

[v2]

Thanks for your response. I think i have found a solution.

[themistoklis]

@v2 

In a new step in PQ you can add the following code:

if Text.Length([Values]) = 6 then Text.Start([Values],2) & "-" & Text.Middle([Values],3,2) & "-" & Text.End([Values],2) else if Text.Length([Values]) = 5 then "0" & Text.Start([Values],1) & "-" & Text.Middle([Values],2,2) & "-" & Text.End([Values],2) else 0

See attached file

[v2 · Accepted solution]

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]

@v2 , Try like

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

[v2]

Thanks for your solution AmitChandak.