Convert Whole Number to Hour, then Merge Hour with Date

[Anonymous]

Hello! New user here, so please excuse the simple question. I have two columns: Column A and Column B. 

Column A is formatted as Date, like so: 6/1/2019

Column B is formatted as Whole Number, like so: 1, 2, 3, 4, ..., 24

Here is what I would like to do, please: how can I convert Column B (Whole Number) to Hour (since there are 24 hours in a day)? Then, how can I merge the converted Column B (now Hour) to Column A (Date) to create Date/Time? Thanks very much.

[ToddChitt · Accepted solution]

I don't have my computer open so cannot verify this suggestion, so take it with a grain of salt:

In Power Query, try converting each column to text data type, then add a third that concatenation them with this bit of M code:

=Text.Combine( { [Date], " ", [Hour], ":00" } )

then convert that to DateTime.

It can probably be done in one long formula using Text.From( ) and DateTime.From( ) functions.

[Jimmy801 · Accepted solution]

Hello

you can try this formula

DateTime.From( [Column A]) + #duration(0,[Column B],0,0)

here the complete code example that you can copy paste in the advanced editor to check how its working

let
	Source = #table
	(
		{"Column A","Column B"},
		{
			{"05/08/20","5"},	{"10/08/20","6"}
		}
	),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type date}, {"Column B", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each DateTime.From( [Column A]) + #duration(0,[Column B],0,0), type datetime )
in
    #"Added Custom"

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[amitchandak]

@Anonymous , Try like

=[column a] + + #duration(0, [column B], 0, 0)

refer

https://natechamberlain.com/2019/08/02/add-or-subtract-hours-from-date-time-values-in-power-bi-using-m-formula-in-power-query-editor/

[ToddChitt · Accepted solution]

I don't have my computer open so cannot verify this suggestion, so take it with a grain of salt:

In Power Query, try converting each column to text data type, then add a third that concatenation them with this bit of M code:

=Text.Combine( { [Date], " ", [Hour], ":00" } )

then convert that to DateTime.

It can probably be done in one long formula using Text.From( ) and DateTime.From( ) functions.