Custom Column- substring of another column

[vanessa]

Hi,

I have a table with a column with date values in the following format:

eg. 20140119

I want to create/ add a new column which will have the following format:

2014-01-19

I tried adding a custom column, but not sure about the function to be used to get a substring.

[Anonymous]

With all these solutions I have a question for the experts: what is more efficient to the model, to make the column on DAX or on Power Query?

Best regards,
Gabriela

[kcantor]

Couldn't you also just, if you wanted to have two columns, do a custom column where the new column equals the old column. Once you create the duplicate column, then convert the date type?

[v-sihou-msft · Accepted solution]

@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,

[vanessa]

@v-sihou-msft

The approach of splitting the column and merging worked. Thank you. 

[sdjensen]

@v-sihou-msft why go through all that when you can convert the column to a date in two steps??

[ankitpatira]

@vanessa you also don't need to create custom column simple select date data type for your column and choose format under Modelling tab.

[vanessa]

@ankitpatira

The original column is of type wholenumber. When I try changing the datatype, it throws the Arithmetic Overflow error

[sdjensen]

@ankitpatira - If you want to make this value a date you need to do it in the Query Editor and you actually have to create the M code yourself unless you have another intermediate step.

You can do it with code like this:

#"Changed Type" = Table.TransformColumnTypes("NameOfPreviousStep",{{"Date", type date}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Changed Type",{{"Date", type datetime}})

[sdjensen]

Hi,

You can do this as a DAX calculated column with this formula:

DateFormat = LEFT( Table1[Date], 4 ) & "-" & MID( Table1[Date], 5, 2 ) & "-" & RIGHT( Table1[Date], 2 )

[vanessa]

@sdjensen

I tried this approach, but when i try to convert the new column from text to date datatype, it throws the following error: