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.
@KNO
Power Query cannot read this Text when there is extra strings others than Date and TIme. With "2013021208352200", I assume you want to get 2013.02.12 08:35:22. To do so, you need to remove the "00" at the end, and add a letter "T" before time to distinguish strings.
You may just change the text datetime to "20130212T083522" in the datasource. Or do some transformations before using DateTime.FromText function:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNDYwMjQysDA2NTIyMFCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date Time" = _t]),
#"Split Column by Positions" = Table.SplitColumn(Source, "Date Time", Splitter.SplitTextByPositions({0, 14}), {"Date Time.1", "Date Time.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Positions",{"Date Time.2"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each Text.Insert(Text.From([Date Time.1]),8,"T")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each DateTime.FromText([Custom])),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.1", type datetime}})
in
#"Changed Type"
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
@KNO
Power Query cannot read this Text when there is extra strings others than Date and TIme. With "2013021208352200", I assume you want to get 2013.02.12 08:35:22. To do so, you need to remove the "00" at the end, and add a letter "T" before time to distinguish strings.
You may just change the text datetime to "20130212T083522" in the datasource. Or do some transformations before using DateTime.FromText function:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNDYwMjQysDA2NTIyMFCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date Time" = _t]),
#"Split Column by Positions" = Table.SplitColumn(Source, "Date Time", Splitter.SplitTextByPositions({0, 14}), {"Date Time.1", "Date Time.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Positions",{"Date Time.2"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each Text.Insert(Text.From([Date Time.1]),8,"T")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each DateTime.FromText([Custom])),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.1", type datetime}})
in
#"Changed Type"
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
Hi @KNO
Please check detailed steps in the PBIX file below about how to get time part from the column and convert it to Time.
https://1drv.ms/u/s!AhsotbnGu1NolB_GPuXRV5NCay0r
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.