Forum Discussion
Custom Column- substring of another column
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.
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:
- In Query Editor, select the date column and click “Split Column By Number of Characters - 2”.
- Repeat step 1 for the separated column.
- 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,
10 Replies
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:
- In Query Editor, select the date column and click “Split Column By Number of Characters - 2”.
- Repeat step 1 for the separated column.
- 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,
- v-sihou-msft why go through all that when you can convert the column to a date in two steps??
The approach of splitting the column and merging worked. Thank you.
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 you also don't need to create custom column simple select date data type for your column and choose format under Modelling tab.
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}})The original column is of type wholenumber. When I try changing the datatype, it throws the Arithmetic Overflow error
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?
