Search code examples
excelpowerquerypowerpivot

Aggregate multiple (many!) pair of columns (Exce)

I have table; The table consists of pairs of date and value columns

 Pair               Pair              Pair            Pair           .... ..... ......

enter image description here

What I need is the sum of all values for the same date. The total table has 3146 columns (so 1573 pairs of value and date)!! with up to 186 entries on row level.

Thankfully, the first column contains all possible date values.

Considering the 3146 columns I am not sure how to do that without doing massivle amount of small steps :(

Solution

  • This shows a different method of creating the two column table that you will group by Date and return the Sum. Might be faster than the List.Accumulate method. Certainly worth a try in view of your comment above.

    • Unpivot the original table
    • Add 0-based Index column; then IntegerDivide by 2
    • Group by the IntegerDivide column and extract the Date and Value to separate columns.
    • Then group by date and aggregate by sum
    let
    Source = Excel.CurrentWorkbook(){[Name="Table12"]}[Content],

//assuming only columns are Date and Value, this will set the data types for any number of columns
    Types = List.Transform(List.Alternate(Table.ColumnNames(Source),1,1,1), each {_, type date}) &
            List.Transform(List.Alternate(Table.ColumnNames(Source),1,1,0), each {_, type number}),
    #"Changed Type" = Table.TransformColumnTypes(Source,Types),

//Unpivot all columns to create a two column table
//The Value.1 table will alternate the related Date/Value
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value.1"),

//add a column to group the pairs of values
//below two lines => a column in sequence of 0,0,1,1,2,2,3,3, ...
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1, Int64.Type),
    #"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Integer-Division", each Number.IntegerDivide([Index], 2), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Integer-Division",{"Index"}),

// Group by the "pairing" sequence,
//  Extract the Date and Value to new columns
// => a 2 column table
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Integer-Division"}, {
        {"Date", each [Value.1]{0}, type date},
        {"Value", each [Value.1]{1}, type number}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Grouped Rows",{"Integer-Division"}),

//Group by Date and aggregate by Sum
    #"Grouped Rows1" = Table.Group(#"Removed Columns1", {"Date"}, {{"Sum Values", each List.Sum([Value]), type number}}),

//Sort into date order
    #"Sorted Rows" = Table.Sort(#"Grouped Rows1",{{"Date", Order.Ascending}})
in
    #"Sorted Rows"

    enter image description here