cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
energyguy
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
ToddChitt
Post Prodigy
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.

View solution in original post

Jimmy801
Super User III
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
    #"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

View solution in original post

3 REPLIES 3
Jimmy801
Super User III
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
    #"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

View solution in original post

amitchandak
Super User IV
Super User IV

@energyguy , 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...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

ToddChitt
Post Prodigy
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.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors