none
Multiplying rows based on date and time

    Question

  • Hi,

    I have a database of all activities performed by a certain resource type with start and end dates/times for each row. I need to show time spent on each activity per calendar month. As long an activity starts and ends within the same month it's not a problem, but there will always be an activity that starts in one month and ends in the next and then I only want the part of the activity that occurred in the first month to show in that month and the remaining part of the activity to show in the month it ends.

    So I need to split those into two or more rows (some activities goes on for over more than two months), is that possible to do in Power Query?

    If not, any suggestions on how to do it would be very appreciated!

    //Caj

    Saturday, December 28, 2013 10:40 PM

Answers

  • Hi Caj,

    This is possible in Power Query, though you will have to write some custom formulas in M.

    Below is one possible approach, it uses an input table with three columns (Start, End and Activity) and applies the following steps:

    1. Given Start and End, create a custom column with a list of months between Start and End dates.
    2. Expand this custom column, to create one row per month per activity.
    3. Given Start/End months and current row month, calculate number of days spent in this month for the current activity.

    Here are the formulas:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}, {"Activity", type text}}),
        InsertedCustom = Table.AddColumn(ChangedType, "Custom", each if((Date.Month([End])-Date.Month([Start]))>=0) then {Date.Month([Start])..Date.Month([End])} else List.Combine({{Date.Month([Start])..12}, {1..Date.Month([End])}})),
        #"Expand Custom" = Table.ExpandListColumn(InsertedCustom, "Custom"),
        InsertedCustom1 = Table.AddColumn(#"Expand Custom", "Custom.1", each if(Date.Month([Start])=Date.Month([End])) then Duration.Days([End]-[Start]) else if([Custom]=Date.Month([Start])) then Duration.Days(Date.EndOfMonth([Start])-[Start]) else if([Custom]=Date.Month([End])) then Duration.Days([End] - Date.StartOfMonth([End]))+1 else if(Date.Year([Start])=Date.Year([End])) then Date.DaysInMonth(#date(Date.Year[Start], [Custom], 1)) else if(Date.Month([Start]) > [Custom]) then Date.DaysInMonth(#date(Date.Year[End], [Custom], 1)) else Date.DaysInMonth(#date(Date.Year[Start], [Custom], 1)))
    in
        InsertedCustom1

    Hope this helps. Note that I didn't account for all possible cases, only enough to give you an idea of how to tackle the problem. The solution above will fail for cases where there are duplicated months within an activity (for example: Start in October 2010 and End in December 2011, where October, November and December would need to be accounted for in 2010 and 2011). You'd have to extend step #1 in this approach to generate also a Year component.

    Thanks,
    M.

    Monday, January 06, 2014 5:47 PM
    Owner
  • I would do this a little differently by converting the table into records and then transforming each record into either its original value (if it doesn't span a month boundary) or by expanding it into multiple records (if it does). One possible implementation looks like this:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Records = Table.ToRecords(Source),
        DateTime.IsSameMonth = (x, y) => Date.Year(x) = Date.Year(y) and Date.Month(x) = Date.Month(y),
        Expand = (x) => List.Generate(
            () => Record.Combine({x, [End=Date.EndOfMonth(x[Start])]}),
            (record) => record[Start] <= x[End],
            (record) => let
                NextStart = Date.StartOfMonth(Date.AddMonths(record[Start], 1)),
                NextEnd = Date.EndOfMonth(NextStart),
                ThisEnd = List.Min({NextEnd, x[End]})
            in
                Record.Combine({record, [Start=NextStart, End=ThisEnd]})),
        Transformed = List.Transform(Records, each if DateTime.IsSameMonth([Start], [End]) then {_} else Expand(_)),
        Combined = List.Combine(Transformed),
        Result = Table.FromRecords(Combined)
    in
        Result

    The use of "List.Generate" is admittedly a little tricky, but the code should be fairly easy to follow. This assumes that the start date/time is in a column named "Start" and the end date/time is in a column named "End".


    • Edited by Curt HagenlocherModerator Monday, January 06, 2014 10:07 PM clarified column names
    • Marked as answer by csten Tuesday, February 11, 2014 8:08 AM
    Monday, January 06, 2014 10:02 PM
    Moderator
  • You can almost do it by changing all instances of "Month" to "Day"; you then just have to fix the helper function to incorporate the day as well. The result:

    let
         Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
         Records = Table.ToRecords(Source),
         DateTime.IsSameDay = (x, y) => Date.Year(x) = Date.Year(y) and Date.Month(x) = Date.Month(y) and Date.Day(x) = Date.Day(y),
         Expand = (x) => List.Generate(
             () => Record.Combine({x, [End=Date.EndOfDay(x[Start])]}),
             (record) => record[Start] <= x[End],
             (record) => let
                 NextStart = Date.StartOfDay(Date.AddDays(record[Start], 1)),
                 NextEnd = Date.EndOfDay(NextStart),
                 ThisEnd = List.Min({NextEnd, x[End]})
             in
                 Record.Combine({record, [Start=NextStart, End=ThisEnd]})),
         Transformed = List.Transform(Records, each if DateTime.IsSameDay([Start], [End]) then {_} else Expand(_)),
         Combined = List.Combine(Transformed),
         Result = Table.FromRecords(Combined)
     in
         Result

    But note that there may be simpler approaches; days are easier to work with than months because their lengths are fixed. I didn't go back and look for an easier solution but maybe you'll find one and post it here! :)

    • Marked as answer by csten Tuesday, February 11, 2014 8:08 AM
    Monday, February 10, 2014 9:32 PM
    Moderator

All replies

  • Hi Caj,

    This is possible in Power Query, though you will have to write some custom formulas in M.

    Below is one possible approach, it uses an input table with three columns (Start, End and Activity) and applies the following steps:

    1. Given Start and End, create a custom column with a list of months between Start and End dates.
    2. Expand this custom column, to create one row per month per activity.
    3. Given Start/End months and current row month, calculate number of days spent in this month for the current activity.

    Here are the formulas:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}, {"Activity", type text}}),
        InsertedCustom = Table.AddColumn(ChangedType, "Custom", each if((Date.Month([End])-Date.Month([Start]))>=0) then {Date.Month([Start])..Date.Month([End])} else List.Combine({{Date.Month([Start])..12}, {1..Date.Month([End])}})),
        #"Expand Custom" = Table.ExpandListColumn(InsertedCustom, "Custom"),
        InsertedCustom1 = Table.AddColumn(#"Expand Custom", "Custom.1", each if(Date.Month([Start])=Date.Month([End])) then Duration.Days([End]-[Start]) else if([Custom]=Date.Month([Start])) then Duration.Days(Date.EndOfMonth([Start])-[Start]) else if([Custom]=Date.Month([End])) then Duration.Days([End] - Date.StartOfMonth([End]))+1 else if(Date.Year([Start])=Date.Year([End])) then Date.DaysInMonth(#date(Date.Year[Start], [Custom], 1)) else if(Date.Month([Start]) > [Custom]) then Date.DaysInMonth(#date(Date.Year[End], [Custom], 1)) else Date.DaysInMonth(#date(Date.Year[Start], [Custom], 1)))
    in
        InsertedCustom1

    Hope this helps. Note that I didn't account for all possible cases, only enough to give you an idea of how to tackle the problem. The solution above will fail for cases where there are duplicated months within an activity (for example: Start in October 2010 and End in December 2011, where October, November and December would need to be accounted for in 2010 and 2011). You'd have to extend step #1 in this approach to generate also a Year component.

    Thanks,
    M.

    Monday, January 06, 2014 5:47 PM
    Owner
  • I would do this a little differently by converting the table into records and then transforming each record into either its original value (if it doesn't span a month boundary) or by expanding it into multiple records (if it does). One possible implementation looks like this:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Records = Table.ToRecords(Source),
        DateTime.IsSameMonth = (x, y) => Date.Year(x) = Date.Year(y) and Date.Month(x) = Date.Month(y),
        Expand = (x) => List.Generate(
            () => Record.Combine({x, [End=Date.EndOfMonth(x[Start])]}),
            (record) => record[Start] <= x[End],
            (record) => let
                NextStart = Date.StartOfMonth(Date.AddMonths(record[Start], 1)),
                NextEnd = Date.EndOfMonth(NextStart),
                ThisEnd = List.Min({NextEnd, x[End]})
            in
                Record.Combine({record, [Start=NextStart, End=ThisEnd]})),
        Transformed = List.Transform(Records, each if DateTime.IsSameMonth([Start], [End]) then {_} else Expand(_)),
        Combined = List.Combine(Transformed),
        Result = Table.FromRecords(Combined)
    in
        Result

    The use of "List.Generate" is admittedly a little tricky, but the code should be fairly easy to follow. This assumes that the start date/time is in a column named "Start" and the end date/time is in a column named "End".


    • Edited by Curt HagenlocherModerator Monday, January 06, 2014 10:07 PM clarified column names
    • Marked as answer by csten Tuesday, February 11, 2014 8:08 AM
    Monday, January 06, 2014 10:02 PM
    Moderator
  • Thanks for your suggestions! I've been working on other things but are back at this problem again and now I'm trying to understand your solutions.

    Seems as if they work as asked for, but I realize when I try your solutions that what I really need is to break this down by day instead of by month and calculate hours per day, then I can aggregate any way I want.

    I've tried modifying your solutions to accomplish that, especially Curt's since it uses both date and time and seemed easier to modify to get what I want, but haven't managed yet. Is it possible to modify your solutions so that they return day by day instead?

    Brgds,

    Caj


    Csten

    Monday, February 10, 2014 4:27 PM
  • You can almost do it by changing all instances of "Month" to "Day"; you then just have to fix the helper function to incorporate the day as well. The result:

    let
         Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
         Records = Table.ToRecords(Source),
         DateTime.IsSameDay = (x, y) => Date.Year(x) = Date.Year(y) and Date.Month(x) = Date.Month(y) and Date.Day(x) = Date.Day(y),
         Expand = (x) => List.Generate(
             () => Record.Combine({x, [End=Date.EndOfDay(x[Start])]}),
             (record) => record[Start] <= x[End],
             (record) => let
                 NextStart = Date.StartOfDay(Date.AddDays(record[Start], 1)),
                 NextEnd = Date.EndOfDay(NextStart),
                 ThisEnd = List.Min({NextEnd, x[End]})
             in
                 Record.Combine({record, [Start=NextStart, End=ThisEnd]})),
         Transformed = List.Transform(Records, each if DateTime.IsSameDay([Start], [End]) then {_} else Expand(_)),
         Combined = List.Combine(Transformed),
         Result = Table.FromRecords(Combined)
     in
         Result

    But note that there may be simpler approaches; days are easier to work with than months because their lengths are fixed. I didn't go back and look for an easier solution but maybe you'll find one and post it here! :)

    • Marked as answer by csten Tuesday, February 11, 2014 8:08 AM
    Monday, February 10, 2014 9:32 PM
    Moderator
  • Thanks, looks like it works perfectly!

    I tried changing month to day but missed the helper function.

    My coding experience is very limited but I realize that this could be very useful. Do you have any suggestions on where I can learn more about power query formulas? Are there any books written on this yet?

    //Caj


    Csten

    Tuesday, February 11, 2014 11:28 AM