Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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:
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,
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
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?
Proud to be a Super User!
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:
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,
The approach of splitting the column and merging worked. Thank you.
@vanessa you also don't need to create custom column simple select date data type for your column and choose format under Modelling tab.
The original column is of type wholenumber. When I try changing the datatype, it throws the Arithmetic Overflow error
@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}})
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 )
I tried this approach, but when i try to convert the new column from text to date datatype, it throws the following error:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |