Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Convert Date/Time in UTC to Local Time with Daylight savings

Hello,   I'm not sure where is the better place to do this manipulation, but I have a column with the date/time as following: The time is in UTC and I want to convert this time to Mountain Day...
Need Help
nehajadhav166's avatar
nehajadhav166
Icon for Resolver I rankResolver I
3 years ago

tmarton ,

 

datetime column is of datatype datetime but still have same error.

What could be area of issue?

here is my m query:

let
Source = Table.SelectColumns(ProductData,{"Assembly_TimeStamp_USPacific","Assembly_Message_Payload_SourceTimestamp_USPacific"}),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Assembly_Message_Payload_SourceTimestamp_USPacific", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "AssemblyTimeStamp", each if [Assembly_TimeStamp_USPacific] = null then [Assembly_Message_Payload_SourceTimestamp_USPacific] else [Assembly_TimeStamp_USPacific]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"AssemblyTimeStamp", type date}, {"Assembly_TimeStamp_USPacific", type date}, {"Assembly_Message_Payload_SourceTimestamp_USPacific", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each HourMinTable1),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"Time"}, {"Custom.Time"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom.Time", type time}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type2", {{"AssemblyTimeStamp", type text}, {"Custom.Time", type text}}, "en-US"),{"AssemblyTimeStamp", "Custom.Time"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"DateTime"),
#"Changed Type3" = Table.TransformColumnTypes(#"Merged Columns",{{"DateTime", type datetime}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"DateTime", "Assembly_DateTime"}}),
date = DateTime.Date("Assembly_DateTime")
in
date

tmarton's avatar
tmarton
Icon for Helper I rankHelper I
3 years ago

Does this happen when you select the column to invoke the function? Also the line below says timestmap but you have it as date, is that correct?

#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"AssemblyTimeStamp", type date}, {"Assembly_TimeStamp_USPacific", type date},

  • nehajadhav166's avatar
    nehajadhav166
    Icon for Resolver I rankResolver I
    3 years ago

    When I try to extract date with below line in advanced editor it gives error:

    date = DateTime.Date(datetimecolumn)

    Column I am using is Assembly_DateTime which is datetime and not Assembly_TimeStamp_USPacific(this is of type date).

    • tmarton's avatar
      tmarton
      Icon for Helper I rankHelper I
      3 years ago

      Ok so you have your datetime column and I am guessing you want to also have just a date column that comes from Assembly_DateTime. You need to duplicate the column before you extract the date. If that is not the case, if you could provide a screen shot of your columns so I can see what you have that could help.