cancel
Showing results for
Did you mean: New Member

## Convert Whole Number to Hour, then Merge Hour with Date

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.

2 ACCEPTED SOLUTIONS  Post Prodigy

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.  Super User III

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

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

3 REPLIES 3  Super User III

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

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  Super User IV

@energyguy , Try like

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

refer

Proud to be a Super User!  Post Prodigy

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. Announcements #### Welcome to the User Group Public Preview  