Custom Column- substring of another column

vanessa

In this scenario, you can change the date format without adding a column. Just split the date column to three columns and then merge them to one. Please refer to following steps:

1. In Query Editor, select the date column and click “Split Column By Number of Characters - 2”.
2. Repeat step 1 for the separated column.
3. Select all three columns and then right click to choose “Merge Columns” with custom separator ‘-’.

You can also click “Advance Editor” and paste below Power Query formulas into it. Then you can finish the format conversion directly. (Table1 is table name and Date is column name here)

let
    Source = Excel.Workbook(File.Contents("Your source file path"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Date", Int64.Type}}),
    #"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Date", type text}}, "en-US"),"Date",Splitter.SplitTextByPositions({0, 4}, false),{"Date.1", "Date.2"}),
    #"Split Column by Position1" = Table.SplitColumn(#"Split Column by Position","Date.2",Splitter.SplitTextByPositions({0, 2}, false),{"Date.2.1", "Date.2.2"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Position1",{"Date.1", "Date.2.1", "Date.2.2"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Date")
in
    #"Merged Columns"

Regards,