Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
Proud to be a Super User! | |
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
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
@Anonymous , Try like
=[column a] + + #duration(0, [column B], 0, 0)
refer
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.
Proud to be a Super User! | |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
49 | |
19 | |
12 | |
11 |