Forum Discussion

smpa01's avatar
smpa01
Community Champion
5 years ago
Solved

Calculated datetime column in Power Query not DAX

My source data is following

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1ACGlWB0krpFSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Durations(#duration(0, 0, 0, 0), 24, #duration(0, 1, 0, 0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

 

 

How can I reach here by creating a concatenation of Column1 and Custom which will yet be a datetime column

 

| Column1  	| Custom     	| datetime             	|
|----------	|------------	|----------------------	|
| 1/1/2021 	| 0.00:00:00 	| 1/1/2021 12:00:00 AM 	|
| 1/1/2021 	| 0.01:00:00 	| 1/1/2021 1:00:00 AM  	|
| 1/1/2021 	| 0.02:00:00 	| 1/1/2021 2:00:00 AM  	|
| 1/1/2021 	| 0.03:00:00 	| 1/1/2021 3:00:00 AM  	|
| 1/1/2021 	| 0.04:00:00 	| 1/1/2021 4:00:00 AM  	|
| 1/1/2021 	| 0.05:00:00 	| 1/1/2021 5:00:00 AM  	|
| 1/1/2021 	| 0.06:00:00 	| 1/1/2021 6:00:00 AM  	|
| 1/1/2021 	| 0.07:00:00 	| 1/1/2021 7:00:00 AM  	|
| 1/1/2021 	| 0.08:00:00 	| 1/1/2021 8:00:00 AM  	|
| 1/1/2021 	| 0.09:00:00 	| 1/1/2021 9:00:00 AM  	|
| 1/1/2021 	| 0.10:00:00 	| 1/1/2021 10:00:00 AM 	|
| 1/1/2021 	| 0.11:00:00 	| 1/1/2021 11:00:00 AM 	|
| 1/1/2021 	| 0.12:00:00 	| 1/1/2021 12:00:00 PM 	|
| 1/1/2021 	| 0.13:00:00 	| 1/1/2021 1:00:00 PM  	|
| 1/1/2021 	| 0.14:00:00 	| 1/1/2021 2:00:00 PM  	|
| 1/1/2021 	| 0.15:00:00 	| 1/1/2021 3:00:00 PM  	|
| 1/1/2021 	| 0.16:00:00 	| 1/1/2021 4:00:00 PM  	|
| 1/1/2021 	| 0.17:00:00 	| 1/1/2021 5:00:00 PM  	|
| 1/1/2021 	| 0.18:00:00 	| 1/1/2021 6:00:00 PM  	|
| 1/1/2021 	| 0.19:00:00 	| 1/1/2021 7:00:00 PM  	|
| 1/1/2021 	| 0.20:00:00 	| 1/1/2021 8:00:00 PM  	|
| 1/1/2021 	| 0.21:00:00 	| 1/1/2021 9:00:00 PM  	|
| 1/1/2021 	| 0.22:00:00 	| 1/1/2021 10:00:00 PM 	|
| 1/1/2021 	| 0.23:00:00 	| 1/1/2021 11:00:00 PM 	|
| 1/2/2021 	| 0.00:00:00 	| 1/2/2021 12:00:00 AM 	|
| 1/2/2021 	| 0.01:00:00 	| 1/2/2021 1:00:00 AM  	|
| 1/2/2021 	| 0.02:00:00 	| 1/2/2021 2:00:00 AM  	|
| 1/2/2021 	| 0.03:00:00 	| 1/2/2021 3:00:00 AM  	|
| 1/2/2021 	| 0.04:00:00 	| 1/2/2021 4:00:00 AM  	|
| 1/2/2021 	| 0.05:00:00 	| 1/2/2021 5:00:00 AM  	|
| 1/2/2021 	| 0.06:00:00 	| 1/2/2021 6:00:00 AM  	|
| 1/2/2021 	| 0.07:00:00 	| 1/2/2021 7:00:00 AM  	|
| 1/2/2021 	| 0.08:00:00 	| 1/2/2021 8:00:00 AM  	|
| 1/2/2021 	| 0.09:00:00 	| 1/2/2021 9:00:00 AM  	|
| 1/2/2021 	| 0.10:00:00 	| 1/2/2021 10:00:00 AM 	|
| 1/2/2021 	| 0.11:00:00 	| 1/2/2021 11:00:00 AM 	|
| 1/2/2021 	| 0.12:00:00 	| 1/2/2021 12:00:00 PM 	|
| 1/2/2021 	| 0.13:00:00 	| 1/2/2021 1:00:00 PM  	|
| 1/2/2021 	| 0.14:00:00 	| 1/2/2021 2:00:00 PM  	|
| 1/2/2021 	| 0.15:00:00 	| 1/2/2021 3:00:00 PM  	|
| 1/2/2021 	| 0.16:00:00 	| 1/2/2021 4:00:00 PM  	|
| 1/2/2021 	| 0.17:00:00 	| 1/2/2021 5:00:00 PM  	|
| 1/2/2021 	| 0.18:00:00 	| 1/2/2021 6:00:00 PM  	|
| 1/2/2021 	| 0.19:00:00 	| 1/2/2021 7:00:00 PM  	|
| 1/2/2021 	| 0.20:00:00 	| 1/2/2021 8:00:00 PM  	|
| 1/2/2021 	| 0.21:00:00 	| 1/2/2021 9:00:00 PM  	|
| 1/2/2021 	| 0.22:00:00 	| 1/2/2021 10:00:00 PM 	|
| 1/2/2021 	| 0.23:00:00 	| 1/2/2021 11:00:00 PM 	|

 

 

Thank you in advance.

 

Need Help
  • Jakinta's avatar
    Jakinta
    5 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1ACGlWB0krpFSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.DateTimes( [Column1], 24, #duration(0, 1, 0, 0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Expanded Custom", "Custom", "datetime"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Duplicated Column",{{"Column1", type date}, {"Custom", type time}, {"datetime", type datetime}})
in
    #"Changed Type2"

2 Replies

  • Jakinta's avatar
    Jakinta
    Solution Sage
    5 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1ACGlWB0krpFSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.DateTimes( [Column1], 24, #duration(0, 1, 0, 0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Expanded Custom", "Custom", "datetime"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Duplicated Column",{{"Column1", type date}, {"Custom", type time}, {"datetime", type datetime}})
in
    #"Changed Type2"
  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    Date and Time can be directly concatenated by "&" operator; thus, create a series of hours by List.Times,

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1ACGlWB0krpFSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Times" = Table.AddColumn(#"Changed Type", "Time", each List.Times(#time(0, 0, 0), 24, #duration(0, 1, 0, 0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Times", "Time"),
    #"Concatenated DateTime" = Table.AddColumn(#"Expanded Custom", "DateTime", each [Date] & [Time])
in
    #"Concatenated DateTime"