Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!The Power BI Data Visualization World Championships is back! It's time to submit your entry. Live now!
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.
Solved! Go to Solution.
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"
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"
| Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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"
| User | Count |
|---|---|
| 18 | |
| 13 | |
| 9 | |
| 8 | |
| 7 |