Forum Discussion
Power Query - Sum all columns values expect one
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 |
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)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
5 Replies
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)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
I'm stuggling to adapt this solution to find the sum of every column, exluding the first seven columns. Is this possible?
Anonymous Thanks for looking into this. Your solution also works. I accepted both as solution. 🙂