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 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

 

 

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])

6 Replies

    • v2's avatar
      v2
      Frequent Visitor
      5 years ago

      Thanks for your solution AmitChandak.

  • themistoklis's avatar
    themistoklis
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      v2
      Frequent Visitor
      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])

    • v2's avatar
      v2
      Frequent Visitor
      5 years ago

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