none
Power query - add time to date RRS feed

  • Question

  • I have a column with a date and a column with a time.

    Ie: 11/27/2016  |  11:48:14

    I want to add a column that contains the sum of the date and the time: 11/27/2016 11:48:14.

    What would be the best syntax to perform that?

    Thanks!

    Friday, December 2, 2016 8:18 PM

Answers

    1. If your date field is recognized as a date field (if not, change type to date by selecting column, choosing Transform tab in ribbon, select Data Type | Date)... and
    2. If your time field is recognized as a time field (if not, change type to time by selecting column, choosing Transform tab in ribbon, select Data Type | Time)... )... then
    3. Select both your date and time columns and then choose "Add Column (tab in ribbon) | Date | Combine Date and Time".

    Here's a sample code. It pulls from a  2x2 table with headers date & time, with a date and a time entry in row two:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}}),
        #"Inserted Merged Date and Time" = Table.AddColumn(#"Changed Type", "Merged", each [Date] & [Time], type datetime)
    in
        #"Inserted Merged Date and Time"


    Ian

    • Marked as answer by wannes1964 Tuesday, December 13, 2016 7:20 PM
    Monday, December 5, 2016 4:48 PM

All replies

  • Just select the 2 columns and select "merge columns"

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Friday, December 2, 2016 9:05 PM
  • I dont think that way you have a datetime format?
    Friday, December 2, 2016 9:30 PM
  • It makes no sense trying to sum a date and a time value (which you cannot do directly anyway), because summing wouldn't increment the date. You should merge the date and time values in a new column. If one column is named Date and the other column is named Time, then the formula in the new column would be =[Date]&[Time]
    Friday, December 2, 2016 11:33 PM
  • Is it then possible to sort the data that way? I need a precision of a second.
    Saturday, December 3, 2016 12:13 PM
  • Is it then possible to sort the data that way? I need a precision of a second.
    Yes. With date and time merged, you can sort to the second. It's easy to test this out with some sample data.
    • Marked as answer by wannes1964 Thursday, December 8, 2016 7:08 PM
    • Unmarked as answer by wannes1964 Tuesday, December 13, 2016 7:21 PM
    Saturday, December 3, 2016 10:16 PM
  • A prerequisite for Matt's and Colin's suggestions is that your date-column is formatted as date (only, date - not datetime!), and the time-column is formatted as time (also: no Datetime).

    Imke Feldmann TheBIccountant.com

    Monday, December 5, 2016 11:02 AM
    Moderator
    1. If your date field is recognized as a date field (if not, change type to date by selecting column, choosing Transform tab in ribbon, select Data Type | Date)... and
    2. If your time field is recognized as a time field (if not, change type to time by selecting column, choosing Transform tab in ribbon, select Data Type | Time)... )... then
    3. Select both your date and time columns and then choose "Add Column (tab in ribbon) | Date | Combine Date and Time".

    Here's a sample code. It pulls from a  2x2 table with headers date & time, with a date and a time entry in row two:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}}),
        #"Inserted Merged Date and Time" = Table.AddColumn(#"Changed Type", "Merged", each [Date] & [Time], type datetime)
    in
        #"Inserted Merged Date and Time"


    Ian

    • Marked as answer by wannes1964 Tuesday, December 13, 2016 7:20 PM
    Monday, December 5, 2016 4:48 PM
  • Hi wannes1964,

    did any of the answers given here solve your problem?

    Then please mark is as answer, so that other people searching in this forum can benefit from it as well.

    If not, please describe which problems still exist. Thank you.


    Imke Feldmann TheBIccountant.com

    Thursday, December 8, 2016 5:40 PM
    Moderator