Expand the continuous date interval

Scenario:
What we have is a date range, and when we need to assign indicators to each day in the date range or calculate by day, we need to extend the date range into a date list.

Table used:

Analyze:

To expand the date, given the date format does not directly construct the list, we can convert the data type by function. Firstly, we have to split the text, then convert the text to a date, then convert the date to a number, use the number to build the list, and finally restore the number to the date.

Expected result:

Detailed steps:

1. Sample data

2. Construct List

In this case, the Start Time column contains not only the date, but also the time. As we know that the date with time is a decimal number when converted to a number, and the decimal number can't build the list. According to the sample data, [Start Time] is an integer except for the first row of each [ID] which is a decimal. The [End Time] is not an integer but can be obtained by converting the integer to time and subtracting 1 second from it, except for the last line which is a decimal.

So we construct a list of integers by rounding up and down, first removing the first and last, and then connecting the first and last individually with & to get the final list.

First and last value:

a={Number.RoundUp(Number.From([StartTime]))..Number.RoundDown(Number.From([End Time]))}

Intermediate values:

b=List.Transform(List.Zip({{[Start Time=[Start Time]]}&List.Transform(a,each [Start Time=DateTime.From(_)]),List.Transform(a,each [End Time=DateTime.From(_)-#duration(0,0,0,1)])&{[End Time=[End Time]]}}),Record.Combine)

We can use the following codes to construct a list with new start time and end time base on specific conditions：

Advanced Editor:

= Table.AddColumn(#"Change Type", "a", each [a={Number.RoundUp(Number.From([Start Time]))..Number.RoundDown(Number.From([EndTime]))},b=List.Transform(List.Zip({{[Start Time=[StartTime]]}&List.Transform(a,each[StartTime=DateTime.From(_)]),List.Transform(a,each[EndTime=DateTime.From(_)-#duration(0,0,0,1)])&{[End Time=[End Time]]}}),Record.Combine)][b])

3. Delete columns

4. Expand List

5. Expand record

Advanced Editor:

let   Source=Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLVNwIiAyNDBUsrAwOIgDlMwNhAKVYnWskJImwBETY0AomDRIwNoCKmViamSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Start Time" = _t, #"End Time" = _t]),

    #"Change Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Start Time", type datetime}, {"End Time", type datetime}}),

    #"Build"=Table.AddColumn(#"Change Type","a",each[a={Number.RoundUp(Number.From([Start Time]))..Number.RoundDown(Number.From([End Time]))},b=List.Transform(List.Zip({{[StartTime=[Start Time]]}&List.Transform(a,each[StartTime=DateTime.From(_)]),List.Transform(a,each[EndTime=DateTime.From(_)-#duration(0,0,0,1)])&{[EndTime=[End Time]]}}),Record.Combine)][b]),

    #"Delete columns" = Table.RemoveColumns(Build,{"Start Time", "End Time"}),

    #"Expand List" = Table.ExpandListColumn(#"Delete columns", "a"),

    #"Expand Record" = Table.ExpandRecordColumn(#"Expand List", "a", {"StartTime", "EndTime"})
in
     #"Expand Record"

Functions used:

List.Transform

List.Zip

#duartion

Author: Link Chen

Reviewer: Kerry & Ula