Forum Discussion
Date TIme Change
- Read in the CSV document.
- Convert column data type to datetime using locale equals english-us.
- Add custom column for finish formula: [Start] + #duration(0,10,0,0)
- See if that gives you what you want
Several problems
- Your date is in the form of a datetime.
- The DateTimeZone.SwitchZone function requires the first argument be a DateTimeZone, not a DateTime
- Your data column is Text.
- You need to set the data type as DateTime to do any functions
Leaving it as DateTime, the following code will add 10 hours:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSNzTTNzIwMlEwMrAyNrAyMFCK1YGIW8DFTUzB4rEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Start = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type datetime}}),
#"Convert to Local" = Table.AddColumn(#"Changed Type","Finish",
each [Start] + #duration(0,10,0,0), type datetime)
in
#"Convert to Local"
Note that to add hours to a datetime is done by adding a duration.
I have converted to this so far
I'm afraid I have no idea what you're doing or how it relates to the code I provided. Please try to clarify.
Providing a better description of exactly what you want for output as well as the code that you are using from the advanced editor might be helpful.
Im not using M Code, apologies
I am using steps in power query. However here is the code - im not a programmer 🙂
let
Source = Csv.Document(File.Contents("csv"),[Delimiter=",", Columns=8, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Added Custom Column" = Table.AddColumn(#"Promoted Headers", "Custom", each Text.Combine({Text.Middle([Start], 3, 3), Text.Start([Start], 2), Text.Middle([Start], 5), " PM"}), type text),
#"Filtered Rows" = Table.SelectRows(#"Added Custom Column", each ([Start] <> "") and ([Finish] <> "")),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Custom", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom.1", each DateTimeZone.SwitchZone(DateTimeZone.From([Custom]), 10))
in
#"Added Custom"
