Power Query - Sum all columns values expect one

[LP280388]

Hi Team,

I'm trying to add the numbers in all columns except one text column.  the columns will increase/decrease based on data population from different DB environments.  So, im trying to capture the column names in a list and trying to pass that to the sum.. 

However, its not working and need some tweaking in the below. Please help.

= Table.AddColumn(#"Pivoted Column", "Addition", each List.Sum(List.RemoveMatchingItems(#"Pivoted Column",{ "my_empid"})), type number)

my data looks like below:  Total column is what Im trying to get as output.   I know this type of table can be achieved in Table or Matrix. But the ultimate goal is to get the "Total Column" next to the empid column which Table/matrix visual doesnt support right now.

empid	assigned	completed	satisfied	inprogress	Total column
xyz1	2	3	1	0	6
xyz2	4	2	2	1	9
xyz3	2	0	2	3	7
xyz4	4	2	2	4	12
xyz5	0	5	5	4	14

[LP280388 · Accepted solution]

I found the answer https://youtu.be/X2AcnH0F6CA?t=4630 and tweaked to my need. Posting it so that others can use it. 

= Table.AddColumn(#"Pivoted Column", "Addition", each List.Sum(Record.FieldValues(Record.SelectFields(_,List.RemoveMatchingItems(Table.ColumnNames(#"Pivoted Column"),{"empid"})))), type number)

[LP280388]

@v-yiruan-msft Thanks for looking into this. Your solution also works.  I accepted both as solution. 🙂

[LP280388 · Accepted solution]

I found the answer https://youtu.be/X2AcnH0F6CA?t=4630 and tweaked to my need. Posting it so that others can use it. 

= Table.AddColumn(#"Pivoted Column", "Addition", each List.Sum(Record.FieldValues(Record.SelectFields(_,List.RemoveMatchingItems(Table.ColumnNames(#"Pivoted Column"),{"empid"})))), type number)

[v-yiruan-msft · Accepted solution]

Hi @LP280388 ,

You can also get it by the following codes, please find the details in the attachment.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqqisMlTSUTICYmMgBrENlGJ1wBIgQROopBFYEiphDBUwgOuESpig6TCBSZhCVZtCMVAiFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [empid = _t, assigned = _t, completed = _t, satisfied = _t, inprogress = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"empid", type text}, {"assigned", Int64.Type}, {"completed", Int64.Type}, {"satisfied", Int64.Type}, {"inprogress", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Total column",  each List.Sum(List.Range(Record.ToList(_),1,Table.ColumnCount(Source)-1)))
in
    #"Added Custom"

And it's glad to hear that your problem has been resolved. Thanks for sharing your solution here. Could you please mark your post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours.  Thank you.

Best Regards

[BlazingTheory]

I'm stuggling to adapt this solution to find the sum of every column, exluding the first seven columns. Is this possible?

[BlazingTheory]

I figured it out:
Here is the m-code for a custom column that will calculate the sum of every column, except for the ones listed after  "Record.RemoveFields": 

List.Sum(Record.ToList(Record.RemoveFields(_, {"Field 1", "Field 2", "Field 3", "Field 4", "Field 5", "Field 6", "Field 7"})))