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

themistoklis's avatar
themistoklis
Community 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])