How to sum over a ever changing list of column names

[Anonymous]

Hi everyone. Please give me some help.

Let's say I have a Table: [table]

I'd like to add a column whose value is the sum over all this table's columns except for column A and B. 

The column names changes every time the table is loaded from its source except for column A and B.

What I am doing now is:

qlist = Table.ColumnNames([table]),
openCAPAlist = List.RemoveItems(qlist, {"A","B"}),
#"Added Custom" = Table.AddColumn([table], "#added column", each List.Sum(Record.ToList(Record.SelectFields(_, openCAPAlist)))),

This works. However when openCAPAlist is big, it takes too long to execute somehow, which is strange given the table has only 19 columns and 74 rows.

Is there any better way to do it?

[Ashish_Mathur]

Hi,

Just thinking aloud - how about selecting the 2 columns (A and B), right clicking and "unpivoting other columns".  Now we can write a DAX measure in the visual to get your desired result.

[Anonymous]

Hi Ashish, appreciate your reply. I am looking to save the summation into a new columns in the [table]. So Is there a way to do what you said in Query M? Also if so, Can you going into a bit detail about how to go from the unpivoted table to the sum of the rest of the columns? To my understanding, after unpivot, I will be left expanded column A and column B and an extra column with all other column names as this new column's entries' value.

[Ashish_Mathur]

Hi,

Could you share some data and show the expected result?

[Anonymous · Accepted solution]

I solved it by using List.Sum(Record.FieldValues(Record.SelectFields(_, List.Difference()))). 

Thanks.