Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
LP280388
Resolver II
Resolver II

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.

empidassignedcompletedsatisfiedinprogressTotal column
xyz123106
xyz242219
xyz320237
xyz4422412
xyz5055414



2 ACCEPTED SOLUTIONS
LP280388
Resolver II
Resolver II

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)

View solution in original post

Anonymous
Not applicable

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"

yingyinr_0-1678931904329.png

 

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

View solution in original post

5 REPLIES 5
LP280388
Resolver II
Resolver II

@Anonymous Thanks for looking into this. Your solution also works.  I accepted both as solution. 🙂

LP280388
Resolver II
Resolver II

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)

Anonymous
Not applicable

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"

yingyinr_0-1678931904329.png

 

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?

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"})))

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.