How to add time and date columns in power query-CodePudding

In the power query, we cannot add type date to type time natively. What are some ways to do this?

CodePudding user response：

There are some code snippets that work. You can create a custom function as well if you use these often.

DateTime.FromText(Text.Combine({Text.From(DateValue), " ", Text.From(TimeValue)}))

Another is to convert them to durations and process them that way.

(DateValue - #date(1900,1,1)) (TimeValue - #time(0,0,0))   #datetime(1900,1,1,0,0,0)

List.Sum({DateValue - #date(1900,1,1), TimeValue - #time(0,0,0),#datetime(1900,1,1,0,0,0)})

Finally

#datetime(Date.Year(DateValue), Date.Month(DateValue), Date.Day(DateValue), Time.Hour(TimeValue), Time.Minute(TimeValue), Time.Second(TimeValue))

CodePudding user response：

Definitely not intuitive for an Excel user, but the Power Query method is:

date & time

let
    Source = Table.FromRecords(
        {[date=#date(2022,1,1), time = #time(1,15,0)]},
        type table [date=date, time=time]),
    #"Added Custom" = Table.AddColumn(Source, "datetime", each [date] & [time], type datetime)
    
in

    #"Added Custom"

In the MS documentation for Power Query operators it shows x & y, where x=date and y=time, =>merged datetime