Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have two columns with times and I would like to expand them so that I can get a row for each hour in between. The times are rounded to the nearest 30 minutes.
record start end
1 6:30am 930am
2 2:00pm 4:30pm
result record
6:30am 1
7:00am 1
8:00am 1
9:00am 1
9:30am 1
2:00pm 2
3:00pm 2
etc...
Solved! Go to Solution.
Here's some sample M code.
The code constructs a list of times "on the hour" between start (shifted to next hour) and end (shifted to previous hour), then adds start & end to that list. It then expands that list to rows.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTKzMjZIzAUyLCGMWJ1oJSMg18jKwKAAJG4CFAcyYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [record = _t, start = _t, end = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"record", Int64.Type}, {"start", type time}, {"end", type time}}), #"Added start2" = Table.AddColumn(#"Changed Type", "start2", each Time.EndOfHour([start]), type time), #"Added end2" = Table.AddColumn(#"Added start2", "end2", each Time.StartOfHour([end]), type time), #"Added timelist" = Table.AddColumn( #"Added end2", "result", each let numtimes = Number.Round(([end2]-[start2])/#duration(0,1,0,0))+1, timelist_middle = if numtimes > 0 then List.Times([start2], numtimes, #duration(0,1,0,0)) else {}, timelist_final = {[start]} & timelist_middle & {[end]} in timelist_final ), #"Removed Other Columns" = Table.SelectColumns(#"Added timelist",{"record", "result"}), #"Expanded timelist" = Table.ExpandListColumn(#"Removed Other Columns", "result"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded timelist",{{"result", type time}}) in #"Changed Type1"
Here's some sample M code.
The code constructs a list of times "on the hour" between start (shifted to next hour) and end (shifted to previous hour), then adds start & end to that list. It then expands that list to rows.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTKzMjZIzAUyLCGMWJ1oJSMg18jKwKAAJG4CFAcyYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [record = _t, start = _t, end = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"record", Int64.Type}, {"start", type time}, {"end", type time}}), #"Added start2" = Table.AddColumn(#"Changed Type", "start2", each Time.EndOfHour([start]), type time), #"Added end2" = Table.AddColumn(#"Added start2", "end2", each Time.StartOfHour([end]), type time), #"Added timelist" = Table.AddColumn( #"Added end2", "result", each let numtimes = Number.Round(([end2]-[start2])/#duration(0,1,0,0))+1, timelist_middle = if numtimes > 0 then List.Times([start2], numtimes, #duration(0,1,0,0)) else {}, timelist_final = {[start]} & timelist_middle & {[end]} in timelist_final ), #"Removed Other Columns" = Table.SelectColumns(#"Added timelist",{"record", "result"}), #"Expanded timelist" = Table.ExpandListColumn(#"Removed Other Columns", "result"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded timelist",{{"result", type time}}) in #"Changed Type1"
Hi,
Can you please elaborate your question, what do you mean by "expand them so that I can get a row for each hour in between".
Any example is much appreciate.
Regards,
Pavan Vanguri.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
87 | |
84 | |
65 | |
49 |
User | Count |
---|---|
140 | |
114 | |
110 | |
59 | |
59 |