How to convert "img_2016-12-16_11:30:35" as datetime?

[Anonymous]

I have column "img_2016-12-16_11:30:35" in Power BI. How to convert this column to datetime format?

[v-yuezhe-msft · Accepted solution]

Hi @Anonymous,

In your scenario, you can also create the following new columns to split your original column.

SplitColumn = REPLACE(Table2[Column1],1,4,"")
ReplaceColumn = SUBSTITUTE(Table2[SplitColumn],"_"," ")


Then select the ReplaceColumn  in Fields Panel, change its data type to Date/Time under modeling ribbon.




Thanks,
Lydia Zhang

[BhaveshPatel]

You can use Replace Values & split column functionality to convert to date time format.

Firstly, Replace _ by space using replace values functionality.

then you can use split column by firstly 4 characters from left side and then split column by 1 character from right side. 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilHKzE2PNzIwNNM1NNI1NIs3NLQyNrAyNo1RUoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [col1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"col1", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","_"," ",Replacer.ReplaceText,{"col1"}),
#"Split Column by Position" = Table.SplitColumn(#"Replaced Value","col1",Splitter.SplitTextByPositions({0, 4}, false),{"col1.1", "col1.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"col1.1", type text}, {"col1.2", type text}}),
#"Split Column by Position1" = Table.SplitColumn(#"Changed Type1","col1.2",Splitter.SplitTextByPositions({0, 1}, true),{"col1.2.1", "col1.2.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position1",{{"col1.2.1", type datetime}, {"col1.2.2", type text}})
in
#"Changed Type2"