Change Number Format to Standard Date Format Power Query

[legalhustler]

I have a column that has various dates in YYMMDD such as 100105 and 181028 and would like to make them appear as in this format MMDDYYYY so that it appears like 01/05/2010 and 10/18/2018, respectively.

How can I convert these numbers to the date format? I tried Column From Examples but it didn't recognize the pattern. Please advise!

 

[sandy666]

try

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
    #"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(Source, {{"YMD", type text}}, "en-GB"), "YMD", Splitter.SplitTextByRepeatedLengths(2), [COLOR="#FF0000"]{"YMD.1", "YMD.2", "YMD.3"}[/COLOR]),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Position",[COLOR="#FF0000"]{"YMD.2", "YMD.3", "YMD.1"}[/COLOR],Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"MDY"),
    [COLOR="#0000FF"][B]#"Changed Type with Locale" = Table.TransformColumnTypes(#"Merged Columns", {{"MDY", type date}}, "en-US")[/B][/COLOR]
in
    #"Changed Type with Locale"[/SIZE]

 

[legalhustler]

try

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
    #"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(Source, {{"YMD", type text}}, "en-GB"), "YMD", Splitter.SplitTextByRepeatedLengths(2), [COLOR="#FF0000"]{"YMD.1", "YMD.2", "YMD.3"}[/COLOR]),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Position",[COLOR="#FF0000"]{"YMD.2", "YMD.3", "YMD.1"}[/COLOR],Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"MDY"),
    [COLOR="#0000FF"][B]#"Changed Type with Locale" = Table.TransformColumnTypes(#"Merged Columns", {{"MDY", type date}}, "en-US")[/B][/COLOR]
in
    #"Changed Type with Locale"[/SIZE]

If I copy your code and put in Advanced Editor it says can't find Table 9. I then tried to copy all the lines except the first and pasted to the bottom of my current code, but it gives me a "Token Comma expected" error. Please advise. Thanks!

 

[legalhustler]

I have a column that has various dates in YYMMDD such as 100105 and 181028 and would like to make them appear as in this format MMDDYYYY so that it appears like 01/05/2010 and 10/18/2018, respectively.

How can I convert these numbers to the date format? I tried Column From Examples but it didn't recognize the pattern. Please advise!

CORRECTION: The bold should be 10/28/2018 when I want reformat 181028

 

[sandy666]

Table9 is a single column with header YMD
Add header YMD to your range (column) with the numbers
select that column and from the ribbon use From Table
in Advanced Editor you'll see the Table name so replace whole code you see with code from the post and replace Table9 to the name of your table

edit:

CORRECTION: The bold should be 10/28/2018 when I want reformat 181028

you want MDY format and it will do that

 

[legalhustler]

I resolved it. Highlighted the Date column, split by delimiter, number of characters = 2, then selected each column that was split in the correct sequential order, clicked Merge Columns, changed the number format to Short Date. Deleted the 3 split columns and that's it!

 

[sandy666]

here is an example excel file

because I've local settings : en-GB but you've en-US so probably you'll need to use Ctrl+Alt+F5 to refresh result table

edit: that's great you resolve it