none
Combine date and time fields to create datetime field RRS feed

  • Question

  • I have a column that is Data type: Time and one that is Data type: Date.  I want to end up with one column that is Date type: Date/Time.  Seems like it should be easy.

    Thanks in advance for your help.

    Tuesday, January 6, 2015 6:59 PM

Answers

  • Hi,

    although this is a dated post, it still appears on top of Google search results for 'power query combine date and time'. I wasn't too happy with the text formatting workaround, but found a much easier way to do it directly in the PQ UI.

    Simply select both the date and time column, then use PQ menu Transform > Date > Combine Date and Time. Voila, it adds a new combined Date-Time column (here called 'Merged') with the following query step:

    = Table.AddColumn(#"Changed Type", "Merged", each [DateCol] & [TimeCol], type datetime)

    So basically the '&' operator does not only allow to concatenate strings, lists, or records, but also merges date and time stamps.

    Hope this will help others who will find this post later.

    • Marked as answer by Joan Tonon Friday, February 22, 2019 1:45 PM
    Friday, February 22, 2019 11:31 AM
  • You can combine the two columns using the Query Editor. Let's assume you have two columns loaded into the Query Editor. The first column has the time column, and the second one has the date, you can follow these steps to combine the two:

    1. Select the two columns.
    2. Go to "Transform" tab, and click the "Merge Columns" (See screenshot below).
    3. In the "Merge Columns" dialog select "Space" in the first dropdown menu, and type the new column name in the second control.
    4. Click "OK".
    5. Now you will see a new column in the Query Editor instead of the previous two columns.
    6. Right click the column header and select Change Type --> Date/Time.
    7. The new column will be converted into a Date/Time column.

    Merge Columns Dialog:



    • Edited by Gil RavivMVP Wednesday, January 7, 2015 12:01 PM Added screenshot
    • Proposed as answer by Gil RavivMVP Wednesday, January 7, 2015 12:02 PM
    • Marked as answer by Joan Tonon Wednesday, January 7, 2015 12:54 PM
    Wednesday, January 7, 2015 11:55 AM

All replies

  • You can combine the two columns using the Query Editor. Let's assume you have two columns loaded into the Query Editor. The first column has the time column, and the second one has the date, you can follow these steps to combine the two:

    1. Select the two columns.
    2. Go to "Transform" tab, and click the "Merge Columns" (See screenshot below).
    3. In the "Merge Columns" dialog select "Space" in the first dropdown menu, and type the new column name in the second control.
    4. Click "OK".
    5. Now you will see a new column in the Query Editor instead of the previous two columns.
    6. Right click the column header and select Change Type --> Date/Time.
    7. The new column will be converted into a Date/Time column.

    Merge Columns Dialog:



    • Edited by Gil RavivMVP Wednesday, January 7, 2015 12:01 PM Added screenshot
    • Proposed as answer by Gil RavivMVP Wednesday, January 7, 2015 12:02 PM
    • Marked as answer by Joan Tonon Wednesday, January 7, 2015 12:54 PM
    Wednesday, January 7, 2015 11:55 AM
  • Thanks so much!  Worked perfectly :)
    Wednesday, January 7, 2015 12:55 PM
  • For use MERGE, both columns need to be text. I'd rather use another thing:

    Go "Add Column" tab -> Insert Custom Column

    In Custom column formula:

    Text.From([date]) & " " & Text.From([time]) //This will concatenate both column in one.

    then you can change column type to Date/Time.

    Thursday, January 8, 2015 7:32 PM
  • The problem with this solution is that it drops the seconds from your time field. So if you are trying to combine Time with seconds and date to find the max effective dated row and you have 2 changes that happened within the same minute, you will end up with 2 rows in your result set and not know which is the correct max effective date.

    More specifically when you convert time with seconds to text it drops the seconds and the merge function converts the date and time field to text before merging them.

    To work around this, first create a custom column that uses Time.ToText() function to convert the time to text. The trick is that you need to use the optional format parameter. For instance Time.ToText([Change Time], "HH:mm:ss"))
    • Edited by DrewGei Tuesday, July 17, 2018 5:44 AM
    Tuesday, July 17, 2018 4:42 AM
  • Hi,

    although this is a dated post, it still appears on top of Google search results for 'power query combine date and time'. I wasn't too happy with the text formatting workaround, but found a much easier way to do it directly in the PQ UI.

    Simply select both the date and time column, then use PQ menu Transform > Date > Combine Date and Time. Voila, it adds a new combined Date-Time column (here called 'Merged') with the following query step:

    = Table.AddColumn(#"Changed Type", "Merged", each [DateCol] & [TimeCol], type datetime)

    So basically the '&' operator does not only allow to concatenate strings, lists, or records, but also merges date and time stamps.

    Hope this will help others who will find this post later.

    • Marked as answer by Joan Tonon Friday, February 22, 2019 1:45 PM
    Friday, February 22, 2019 11:31 AM