Automatic sum of row values with dynamic number of columns

[Anonymous]

Hello,

I have the following table in Power Query and often there are new (data type: whole number) columns added or removed from this table which is stored in an Excel spreadsheet. In Power Query, i have added a step to sum all the values on each row, except for the date. However, this step needs to be manually updated every time that a new column is added/removed.

Is it possible to automate this Sum column so that this step no longer needs to be updated manually in Power Query?

This table is shown in a table visualization in Power BI. Maybe a formula to always sum all values on each row (irrespective of the number of columns) and exclude Date column? Any help is much appreciated!

For example, this is the original table:

Date	Stock	Returns	Defective	Sold	Excess	Expired
08/08/2022	3	4	8	2	8	11
01/01/2022	6	9	17	6	2	10
27/05/2022	2	5	10	3	6	12
20/10/2022	9	3	16	3	5	12
07/08/2022	5	6	15	7	3	15
12/08/2022	2	2	10	2	3	11
17/08/2022	7	8	22	9	3	19
20/08/2022	3	3	13	4	7	13
22/08/2022	6	3	18	7	2	18
29/08/2022	3	3	14	8	9	14
07/09/2022	2	7	17	5	2	17
16/09/2022	3	7	22	3	6	21

A few days later, a new column has been added, so now the sum column needs to automatically include this new column.

Date	Stock	Returns	Defective	Sold	Excess	Expired	Overdue
08/08/2022	3	4	8	2	8	11	9
01/01/2022	6	9	17	6	2	10	5
27/05/2022	2	5	10	3	6	12	6
20/10/2022	9	3	16	3	5	12	6
07/08/2022	5	6	15	7	3	15	4
12/08/2022	2	2	10	2	3	11	2
17/08/2022	7	8	22	9	3	19	8
20/08/2022	3	3	13	4	7	13	3
22/08/2022	6	3	18	7	2	18	3
29/08/2022	3	3	14	8	9	14	3
07/09/2022	2	7	17	5	2	17	3
16/09/2022	3	7	22	3	6	21	7

[LinVen]

Hi 

This seems so close to a perfect solution for me, but i need to exclude an employee number field as well as the date

Any way to tell this to exclude 2x columns?

I tried:
= Table.AddColumn(#"Renamed Columns", "Custom", each List.Sum(Record.FieldValues(Record.SelectFields(_, List.Difference(Table.ColumnNames(#"Renamed Columns"), {"Date"}, {Employee ID})))))

(my prev step being a renamed columns) But no joy...

[AntonioM]

Hi @LinVen ,

Try putting both column names in the same list, so:

= Table.AddColumn(#"Renamed Columns", "Custom", each List.Sum(Record.FieldValues(Record.SelectFields(_, List.Difference(Table.ColumnNames(#"Renamed Columns"), {"Date", "Employee ID"} )))))

Change {"Date"} to {"Date", "Employee ID"}

[AntonioM]

Hi @Anonymous ,

For this I think you would want to "unpivot other columns". This will keep the date columns, unpivot and then sum up all the values (no matter what the columns are). 

This is assuming you'll never have more than one row for any date, as it will sum them all into one if you do.

let
    Source = (your source here)
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Stock", Int64.Type}, {"Returns", Int64.Type}, {"Defective", Int64.Type}, {"Sold", Int64.Type}, {"Excess", Int64.Type}, {"Expired", Int64.Type}, {"Overdue", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Date"}, {{"Sum", each List.Sum([Value]), type number}})
in
    #"Grouped Rows"

• Unpivot other

• Group by

[Anonymous]

@AntonioM Thanks for your reply! I tried your method and it works but is it possible to keep all the other columns and have the new Sum column added at the far right side?

This is the original table in Power Query:

The result which is missing the other columns:

[AntonioM · Accepted solution]

Hi @Anonymous,

You could keep a copy of the original table and then merge the sum column into it, however using this idea will keep to one step in one query,

= Table.AddColumn(#"Changed Type", "Custom", each List.Sum(Record.FieldValues(Record.SelectFields(_, List.Difference(Table.ColumnNames(#"Changed Type"), {"Date"})))))

where #"Changed Type" is the previous step. This will sum up the value for every column which isn't "Date".