none
Aggregate all columns in a table RRS feed

  • Question

  • Hi,

    I'm pretty much new to Power Query M Code in Excel (although I do have experience with VBA).  I found an article that gives sample code to dynamically 'expand all' columns in a PowerQuery:

    https://blog.crossjoin.co.uk/2014/05/21/expanding-all-columns-in-a-table-in-power-query/

    However, I would like to modify the ExpandAll() function so it effectively becomes AggregateAll() (using the Sum function as my aggregator).

    I tried a few things, but being an M Code novice I'm pretty much shooting in the dark.  I assume I have to modify the line:

         ExpandedTable = if CanExpandCurrentColumn then Table.ExpandTableColumn(TableToExpand, ColumnName, ColumnsToExpand, NewColumnNames) else TableToExpand,
    
    I guess I need to replace Table.ExpandTableColumn with Table.AggregateTableColumn()


    Can anyone help?

    Tuesday, November 20, 2018 8:41 AM

Answers

  • try this:

    let Source = Table.FromColumns( {{"Fruit","Fruit","Fruit","Meat","Meat"},{3,4,2,9,7}, {6,3,1,8,8}, {7,1,7,7,8}}, {"Category", "Jan-18", "Feb-18", "Mar-18"}), ChType = Table.TransformColumnTypes(Source,{{"Jan-18", Int64.Type}, {"Feb-18", Int64.Type}, {"Mar-18", Int64.Type}, {"Category", type text}}),

    Unpivot = Table.UnpivotOtherColumns(ChType, {"Category"}, "Attribute", "Value"), Group = Table.Group(Unpivot, {"Category", "Attribute"}, {{"Total", each List.Sum([Value]), type number}}), Pivot = Table.Pivot(Group, List.Distinct(Group[Attribute]), "Attribute", "Total", List.Sum) in Pivot

     The only reference is your first column "Category". You can have as many months as you want, it will work



    Tuesday, November 20, 2018 11:07 AM

All replies

  • I am pulling in data from an external source (another Excel file, generated by someone else).  That data has a few 'fixed' columns that are always in place, and then a variable number of columns, which each represent a different month. 

    I would like to aggregate the data for each month column.

    I can build a Power Query manually to do this, but the M Code is 'fixed' according to the particular months that were in my source table when I built the query (e.g. maybe the M Code refers to Jan-18, Feb-18, ... Aug-18).

    But, next time I run the query, the source file may have changed and now includes columns Mar-18, ... , Dec-18.  My Power Query now won't work, as some columns are missing and new ones have been added, meaning I have to manually update the Power Query code (and as this spreadsheet will be used by people who have never heard of Power Query, this is not practical).

    The code given in the hyperlink does this perfectly, assuming all I wanted to do was to 'Expand' a grouped column.  But, I don't want to Expand, I want to Aggregate! :(

    Tuesday, November 20, 2018 9:03 AM
  • Then what you want is to add a column showing your TOTAL YTD ?

    • Edited by anthony34 Tuesday, November 20, 2018 9:49 AM
    Tuesday, November 20, 2018 9:48 AM
  • No, sorry.

    After various steps in my query building, I get to a point where my query contains 2 columns.  First column is 'category', and the second column contains the results of the previous 'group by' operation (i.e. it contains tables).  These tables contain data for months, e.g. Jan/Feb/Mar/etc.

    So, (sorry for my poor formatting):

    Category / GroupByColumn

    Fruit / Table, contains {3 / 6 / 7}

    Fruit / Table, contains {4 / 3 / 1}

    Fruit / Table, contains {2 / 1 / 7}

    Meat / Table, contains {9 / 8 / 7}

    Meat / Table, contains {7 / 8 / 8}

    I want this result:

    Category / Jan / Feb / Mar

    Fruit / 9 / 10 / 15

    Meat / 16 / 16 / 15

    So, manually, this is trivial.  I just click the 'expand' button at the top of the second column and in the dialogue box select the 'aggregate' function, select the check boxes for Jan, Feb and Mar, and I get exactly what I need.  Excel generates the appropriate M Code, with 'Jan', 'Feb' and 'Mar' physically appearing in the M code.

    But, next time my source data changes, I might not have data for 'Jan' any more, and 'Apr' and 'May' might be added.  I would have to update the query M code to account for this.

    The code referenced in the hyperlink (did you get a chance to read this Anthony?), does this automatically ... i.e. all columns that contain tables are auto-expanded, without needing to hard-code column names into the M code.  I just want to do the same, but with 'aggregate' instead of 'expand'.

    Tuesday, November 20, 2018 10:25 AM
  • try this:

    let Source = Table.FromColumns( {{"Fruit","Fruit","Fruit","Meat","Meat"},{3,4,2,9,7}, {6,3,1,8,8}, {7,1,7,7,8}}, {"Category", "Jan-18", "Feb-18", "Mar-18"}), ChType = Table.TransformColumnTypes(Source,{{"Jan-18", Int64.Type}, {"Feb-18", Int64.Type}, {"Mar-18", Int64.Type}, {"Category", type text}}),

    Unpivot = Table.UnpivotOtherColumns(ChType, {"Category"}, "Attribute", "Value"), Group = Table.Group(Unpivot, {"Category", "Attribute"}, {{"Total", each List.Sum([Value]), type number}}), Pivot = Table.Pivot(Group, List.Distinct(Group[Attribute]), "Attribute", "Total", List.Sum) in Pivot

     The only reference is your first column "Category". You can have as many months as you want, it will work



    Tuesday, November 20, 2018 11:07 AM
  • Jim

    with Table.AggregateTableColumn (+ dynamic 1st column name)

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        sourceColumNames = Table.ColumnNames(Source),
        firstColumnName = List.FirstN(sourceColumNames, 1),
        columnsToSum = List.Skip(sourceColumNames, 1),
    
        aggregList = List.Transform(columnsToSum, each
            {_, List.Sum, _}),
        rowsGrouped = Table.Group(Source, firstColumnName, {{"Group", each _, type table}}),
        columnsToSumAggregated = Table.AggregateTableColumn(rowsGrouped, "Group", aggregList),
        typeChanged = Table.TransformColumnTypes(columnsToSumAggregated,
            List.Transform(columnsToSum, each {_, type number}))
    in
        typeChanged

    EDIT: last step (typeChanged) not required if the column Type is provided in aggregations list:

        aggregList = List.Transform(columnsToSum, each
            {_, List.Sum, _, type number}),
        rowsGrouped = Table.Group(Source, firstColumnName, {{"Group", each _, type table}}),
        columnsToSumAggregated = Table.AggregateTableColumn(rowsGrouped, "Group", aggregList)
    in
        columnsToSumAggregated

    Tuesday, November 20, 2018 3:50 PM
  • Hi Jim

    Did any proposal helped/solved your problem? If so please mark it as Answer ==> Can help others with same/similar scenario. Thanks

    Friday, November 23, 2018 12:36 AM
  • Sorry for delay in replying ... that worked perfectly!  Many thanks ...
    Monday, November 26, 2018 12:22 PM