powerquery - sum of column using its order - not using header

[JiriH]

Hello, I have a silly question - I need tu sum 12 columns to make Moving Annual Total and I need to do it in power query. But headers of that columns are changing - they contains month names. So I found something, but i cannot make it work - here is syntax example I need to correct from you:

= Table.AddColumn(Source, "MAT", each List.Sum({ Table.ColumnNames(Source){1}, Table.ColumnNames(Source){2}}), type number)

Thank you very much in andvance to helping me with my problem . Jiri

 

[Alex Blakenburg]

The simplest solution I have seen is this one:

• Select all the columns you want to add up and then go Add Column > Standard > Add
• You will get something that looks like this
  = Table.AddColumn(#"Pivoted Column", "Addition", each List.Sum({[#"28/02/2015"], [#"31/01/2015"], [#"31/12/2014"], [#"30/11/2014"], [#"31/10/2014"], [#"30/09/2014"], [#"31/08/2014"], [#"31/07/2014"], [#"30/06/2014"], [#"31/05/2014"], [#"30/04/2014"], [#"31/03/2014"]}), Int64.Type)
• Replace everything in Red with this
  List.Sum(Record.ToList(Record.RemoveFields(_, {"Fixed", "Column", "Name"})))
• Then replace {"Fixed","Column","Name"}, with the column names that are not going to change every time you run the report and you don't want to included in the Sum formula.
  eg. in my case I just only had 1 column to exclude which was the sales products in the column "Sales" so I the exclusion array became just {"Sales"}

If your query is including more months and you want all the months in your output but only add some of the month's it will be more complicated then this.

Reference for the above was:
Wedge > How to reference dynamic column names in Power Query (M language)

 

[RoryA]

How should the code know which fields to sum? Are they the last 12 fields, for example, or the only fields, or something else?

 

[JiriH]

There is more than 12 fields and order of them is from oldest to newest - so I can say: "sum of these 12 colums is MAT value". But headers are changing (months). And I know, that there is possibility to navigate to column by its order number (for example - Table.ColumnNames(Source){1}). But I cannot solve the right syntax fot List.Sum with it... In my example are only two columns because of lenght of it...

 

[shaowu459]

Sum the last 12 columns?

工作簿1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13	Column14
2	1	2	3	4	5	6	7	8	9	10	11	12	13	14
3	2	3	4	5	6	7	8	9	10	11	12	13	14	15
4	3	4	5	6	7	8	9	10	11	12	13	14	15	16
5	4	5	6	7	8	9	10	11	12	13	14	15	16	17
6	5	6	7	8	9	10	11	12	13	14	15	16	17	18
7
8	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13	Column14	Sum
9	1	2	3	4	5	6	7	8	9	10	11	12	13	14	102
10	2	3	4	5	6	7	8	9	10	11	12	13	14	15	114
11	3	4	5	6	7	8	9	10	11	12	13	14	15	16	126
12	4	5	6	7	8	9	10	11	12	13	14	15	16	17	138
13	5	6	7	8	9	10	11	12	13	14	15	16	17	18	150
Sheet1

Cell Formulas
Range	Formula
A3:N6	A3	=A2+1

 

[shaowu459]

Code for post #5.

let
    Source = Excel.CurrentWorkbook(){[Name="tbl"]}[Content],
    res = Table.AddColumn(Source,"Sum",each List.Sum(List.LastN(Record.ToList(_),12)))
in
    res