Expanding Events by Date

[Mentzer]

Hello people,

Let's suppose we have some events with an Initial Date and a Final Date. If the Final Date ends on a different day from the Initial Day, I would like to create a row for each different day. 

Below is an example to make it clearer:

Is there any way to do this? Let's consider that we don't have just 4 IDs (events), we will have new events as time goes by, this image is just an example.

Best regards.

[v-jingzhang · Accepted solution]

Hi @Mentzer 

You can use Power Query Editor to transform the table. Steps are:

1. Add a Date list column to have all dates from initial date to final date on each row. 

2. Expand above list column to new rows. 

3. Add a new Initial Date column

4. Add a new Final Date column

5. Select only ID, New Initial Date and New Final Date columns and remove other columns. 

Full code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc1BCsAgDETRq0jWgsmoLWbXAxS6F+9/jUakBWu383lJrSTkCQwEQYjiRDRvmou7zjXsKrmH5ithzUUZr4tBgs1QKzG5Y6j4UaxiiieFPoM18fMrTSfZwK8Zc//Ubg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Initial Date" = _t, #"Final Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Initial Date", type datetime}, {"Final Date", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each List.Dates(Date.From([Initial Date]), Duration.Days(Date.From([Final Date]) - Date.From([Initial Date])) + 1, #duration(1,0,0,0))),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Date", "New Initial Date", each if Date.From([Initial Date]) = [Date] then [Initial Date] else [Date] & #time(0,0,0)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "New Final Date", each List.Min({[Date] & #time(23,59,59), [Final Date]})),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"ID", "New Initial Date", "New Final Date"})
in
    #"Removed Other Columns"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.