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