Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Funky date format from CSV file

Hi I am trying to import some data from a CSV file, that has a date column. However the way the date is written to the date column in the CSC file, is pretty strange. For instance in the file date 09...
Need Help
  • danextian's avatar
    danextian
    8 years ago

    Hi Anonymous,

     

    Assuming the lengths of you month and year are always 2 and 4 respectively, you may use this formula in PQ:  Text.PadStart(Text.Start([Date Text], Text.Length([Date Text])-6), 2, "0") & "-" &
    Text.Start(Text.End([Date Text], 6), 2) & "-" & Text.End([Date Text], 4)

     

    Just replace [Date Text] with your date column.

     

     

danextian's avatar
danextian
Icon for Super User rankSuper User
8 years ago

Hi Anonymous,

 

Assuming the lengths of you month and year are always 2 and 4 respectively, you may use this formula in PQ:  Text.PadStart(Text.Start([Date Text], Text.Length([Date Text])-6), 2, "0") & "-" &
Text.Start(Text.End([Date Text], 6), 2) & "-" & Text.End([Date Text], 4)

 

Just replace [Date Text] with your date column.

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Thanks danextian it works perfectly. 

     

    I have checked the file, and it seems like there setup of the date is always as i described, so i think your solution should work all the time.