Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Tried to change the format to Date/Time. Error saying 'We couldn't parse the input provided as DateTime value.' shows up.
The column in question is Date TIme. Merged Date and Time columns to create it.
So need to convert 0800 in the first row to 08:00.
Solved! Go to Solution.
Hi @Anonymous
You can add a custom column, like below
=Text.Combine({Text.Start([Time], 2), ":", Text.Middle([Time], 2, 2)})
Also, see the attached for detail
I did this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDXNTDVNTIwMlDSUTKwMDDIKCpWitUBSpghSRgaGyMkjJAlDJB0mOgaG8AlTFB1GMHtsDQxBUvEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Column1", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","hrs","",Replacer.ReplaceText,{"Column1"}),
#"Split Column by Position" = Table.SplitColumn(#"Replaced Value", "Column1", Splitter.SplitTextByPositions({0, 2}, true), {"Column1.1", "Column1.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column1.1", Int64.Type}, {"Column1.2", Int64.Type}}),
#"Added Custom Column" = Table.AddColumn(#"Changed Type1", "Custom", each let splitColumn12 = Splitter.SplitTextByDelimiter(":", QuoteStyle.None)(Text.PadStart(Text.From([Column1.2], "en-US"), 2, ":")) in Text.Combine({Text.From([Date], "en-US"), " ", Text.From([Column1.1], "en-US"), ":", Text.From([Column1.2], "en-US"), splitColumn12{1}?}), type text),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom Column",{{"Custom", type datetime}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Column1.1", "Column1.2"})
in
#"Removed Columns"
Hi @Anonymous
You can add a custom column, like below
=Text.Combine({Text.Start([Time], 2), ":", Text.Middle([Time], 2, 2)})
Also, see the attached for detail