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.

     

     

jthomson's avatar
jthomson
Icon for Solution Sage rankSolution Sage
8 years ago

If you know consistently that you're going to have four figures for the year and then two figures for the month, then you can look to extract the last six digits in Power Query - 

 

newcolumn = Text.End (currentdatecolumn,6)

 

Then use a similar thing and Text.Start to make columns for your year and month. Getting the day might be a bit trickier given it looks like it can be one or two digits, I'd go with Text.Length to work out if your original date is seven or eight digits, then take six away from that value and Text.Start using that value to grab the day of the month