Search code examples
excelpowerquerym

Power Query date from yymmdd to dd/mm/yyyy

I am trying to automate a table which source are CSV docs that are dropped into an FTP. As this is the case, the source name for each daily file, comes with the date in the first 6 letter EG"20041712182E210.txt". What I am trying to do is to duplicate the column and then extract the first 6 characters to leave 200417 on another column so then I can convert into a date format.

Formula

    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column1", "Column1 - Copy"),
    #"Extracted Text Range" = Table.TransformColumns(#"Duplicated Column", {{"Column1 - Copy", each Text.Middle(_, 10, 2), type text}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Extracted Text Range", "Source.Name", "Source.Name - Copy"),
    #"Extracted Text Range1" = Table.TransformColumns(#"Duplicated Column1", {{"Source.Name - Copy", each Text.Middle(_, 0, 6), type text}}),

in

#"Extracted Text Range1"

However, of course excle doesn't identify yyddmm as an appropriate format. How can I modify the function so excel can recognize it as dd/mm/yyyy?

Thanks all

Solution

  • Assuming column named Column1 contains YYMMDD

    format column as text

    add custom column with below formula to convert to a system usable date

    = Date.FromText("20"&[Column1])

    alternately, format column as text , then transform existing column with

    #"Transform" = Table.TransformColumns(#"Prior Step Name",{{"Column1", each Date.FromText("20"&_), type date}})