Combining and transposing multiple timeseries with Excel PowerQuery

Question

I have a table of timeseries data, where each entry is associated with a date interval. Is there a way to combine and merge different timeseries into a single time series?

Given input Table1:

|Role|From      |To        |Value|
|A   |01.01.2017|15.01.2017|    1|
|A   |16.01.2017|01.02.2017|    2|
|A   |02.02.2017|28.02.2017|    2|
|B   |16.01.2017|05.03.2017|    5|
|B   |06.03.2017|31.03.2017|    7|

Can I transform this automatically using PowerQueryinto the following (note the use of merged intervals):

|From|01.01.2017|16.01.2017|02.02.2017|01.03.2017|06.03.2017|
|To  |15.01.2017|01.02.2017|28.02.2017|05.03.2017|31.03.2017|
|A   |         1|         2|         2|          |          |
|B   |          |         5|         5|         5|         7|
|Sum |         1|         7|         7|         5|         7|

Answer 1

Here is a version of your code. It is a little shorter and probably more straightforward. Also it returns exactly the same result as in the example.

let
    // Data source
    SourceTable = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Data = Table.TransformColumnTypes(SourceTable,{{"Role", type text}, {"From", type datetime}, {"To", type datetime}, {"Value", Int64.Type}}),

    //Get a list of roles. 
//    Roles = {"A", "B", "C", "D"}, //Example line. Comment it
    Roles = List.Transform(Table.Distinct(Table.SelectColumns(SourceTable, {"Role"}))[Role], each Text.Trim(Text.Upper(_))),
    // Create table of all possible intervals
    UnionTables = Table.TransformColumnTypes(
                        Table.Combine({
                               Table.SelectColumns(SourceTable, "From"), 
                               Table.SelectColumns(Table.AddColumn(Table.SelectColumns(SourceTable, "To"), "From", each Date.AddDays([To], 1)), {"From"}) //Add 1 day to ensure next interval starts at the same date to produce no fake 1-day intervals
                        }), 
                  {{"From", type datetime}}),
    SortDates = Table.Sort(Table.Distinct(UnionTables), {{"From", Order.Ascending}}),
    AddIndex = Table.AddIndexColumn(SortDates, "Index", 0, 1),
    AllIntervals = Table.RemoveRowsWithErrors(Table.AddColumn(AddIndex, "To", each Date.AddDays(AddIndex[From]{[Index] + 1}, -1), type datetime)), //Substract 1 day to revert intervals to their original values
    AddColumns = Table.Combine(List.Transform(Roles, (Role) => Table.AddColumn(Table.SelectColumns(AllIntervals, {"From", "To"}), Role, (x) => List.Sum(Table.SelectRows(Data, (y) => Text.Upper(Text.Trim(y[Role])) = Role and ((y[From] >= x[From] and y[To] <= x[To]) or (y[From] <= x[From] and y[To] >= x[To])))[Value]), type number))),
    GetDistinctRows = Table.Distinct(AddColumns),
    FilterOutNulls = Table.SelectRows(GetDistinctRows, each List.MatchesAny(Record.FieldValues(Record.SelectFields(_, Roles)), (x) => x <> null) /*List.Match(Table.ColumnNames(AddColumns), Roles) <> null*/),
    AddSum = Table.AddColumn(FilterOutNulls, "Sum", each List.Sum(Record.FieldValues(Record.SelectFields(_, Roles)))),
    ListOfNonNullColumns = 
        List.Select(
            List.Transform(Roles, (x) => if (
                Table.RowCount(
                    Table.SelectRows(AddSum, (y) => 
                          List.Sum(
                                Record.FieldValues(
                                      Record.SelectFields(y, {x})
                                ) 
                          ) > 0
                    )
                )
            ) > 0 then x else null)
        , each _ <> null),

    CleanAndReorder =  Table.SelectColumns(AddSum, List.Combine({{"From", "To"}, ListOfNonNullColumns, {"Sum"}})),
    DemoteHeaders = Table.DemoteHeaders(CleanAndReorder),
    Result = Table.Transpose(DemoteHeaders)
in
    Result

Answer 2

I figured out one way to do it, code in M for power query below with comments on individual steps:

let
    // Data source
    SourceTable = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Data = Table.TransformColumnTypes(SourceTable,{{"Role", type text}, {"From", type datetime}, {"To", type datetime}, {"Value", Int64.Type}}),

    // Create table of all possible intervals
    AllDates =      Table.Sort(
                     Table.Distinct(
                       Table.Combine({
                               Table.RenameColumns(Table.SelectColumns(SourceTable, "From"), {"From", "Date"}), 
                               Table.RenameColumns(Table.SelectColumns(SourceTable, "To"), {"To", "Date"})
                       })
                      ), {{"Date", Order.Ascending}}
                     ), 
    RenameFrom = Table.RenameColumns(
                       Table.AddIndexColumn(AllDates, "Index", 0, 1), {"Date", "From"}
                 ),
    AllIntervals = Table.RemoveRowsWithErrors( Table.AddColumn(RenameFrom, "To", each RenameFrom[From]{[Index] + 1})),


    // Join Data with all possible intervals
    InsertedCustom = Table.AddColumn(Data, "temp", each AllIntervals),
    ExpandedTable = Table.ExpandTableColumn(InsertedCustom, "temp", { "From", "To" }, { "From2", "To2" }),
    ExpandedTableWithValidValue = Table.AddColumn(ExpandedTable, "ValidValue", each ( if ([From] < [To2] and [To] > [From2]) then [Value] else 0) ), 
    ExpandedTableRevised = Table.RenameColumns( Table.RemoveColumns (ExpandedTableWithValidValue , { "Value", "From", "To" } ) , {  { "ValidValue", "Value" }, {"From2", "From"}, { "To2", "To" } } ),

    // Group and sum Data
    #"Grouped Rows" = Table.Group(ExpandedTableRevised, List.RemoveItems(Table.ColumnNames(ExpandedTableRevised), { "Value" } ), {{"Value", each List.Sum([Value]), type number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Role]), "Role", "Value", List.Sum),
    #"Summed By Role" = Table.AddColumn(#"Pivoted Column", "Sum", each List.Sum(Record.ToList(Record.RemoveFields(_, { "From", "To" }))) ),

    // Transpose data with intervals, values and sum to target result structure
    ResultDataTable = Table.Transpose(#"Summed By Role"),

    // Create First column with labels for result structure
    FirstColumnRoles= Table.Distinct(Table.SelectColumns(SourceTable, "Role")),
    FirstColumnWithFromToLabels =  Table.InsertRows(FirstColumnRoles, 0, { [Role="From"], [Role="To"] }),
    FirstColumnWithSum = Table.InsertRows ( FirstColumnWithFromToLabels , Table.RowCount(FirstColumnWithFromToLabels ), {[Role="Sum"]}),
    FirstColumn = Table.AddIndexColumn(FirstColumnWithSum , "Index", 0, 1),

    // Merge First column of labels with data structure and clean up redundant columns
    ResultTable = Table.RemoveColumns(
                          Table.ReorderColumns( Table.Join( FirstColumn, "Index", Table.AddIndexColumn(ResultDataTable , "Index", 0, 1), "Index"), { "Index", "Role"}),
                          "Index"
                      ),

    Custom1 = ResultTable

in
    Custom1