Forum Discussion
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-03-2018 is written 9032018. 31-12-2018 is written 31122018. Any fancy ideas for how to convert to correct data format in this case?
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.
3 Replies
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
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.
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.
