Sum total in Power Query when pivoting a column

[albertan]

Hello,
I have data in Power Query for May (5) and June (6) . I pivoted these columns now I want to add them up. When I add them I use sum formula but it generates it in M like this:

Table.AddColumn(#"Replaced Value", "Addition", each List.Sum({[5], [6]}), type number)

I want to make it dynamic so that next month when July is added the sum column is updated.

Is there any other way to sum up columns in Power Query?

Thanks

 

[GraH]

Given a starting lay out like

Book1
	C	D	E	F	G	H	I
2	Data	Jan	Feb	Mar	Apr	May	Jun
3	a	1,585984	1,138796	5,683209	7,106207	7,589446	4,365661
4	b	5,695318	3,245678	8,234361	2,429451	1,781528	1,615019
5	c	6,882695	6,703107	7,830778	4,100472	9,902449	9,264326
6	d	6,51787	2,117978	2,405021	2,642302	8,170419	7,328626
7	e	2,546614	5,218132	9,26973	8,937019	6,618199	6,870404
Sheet1

One way via PQ

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SetColTypes = Table.TransformColumnTypes(Source,{{"Data", type text}, {"Jan", type number}, {"Feb", type number}, {"Mar", type number}, {"Apr", type number}, {"May", type number}, {"Jun", type number}}),
    AddTotCol = Table.AddColumn(SetColTypes, "Sum Cols", each
  
    let
          ColNames = Table.ColumnNames(SetColTypes)
        , ListMths = {
                      "Jan", "Feb", "Mar"
                     , "Apr", "May", "Jun"
                     , "Jul", "Aug", "Sep"
                     , "Oct","Nov", "Dec"
                      }
        ,MthCols = List.Intersect({ColNames, ListMths})
        ,ColTotal = List.Sum(Record.FieldValues(Record.SelectFields(_,MthCols)))
    in
       ColTotal )
in
    AddTotCol

Results in

Book1
	A	B	C	D	E	F	G	H
1	Data	Jan	Feb	Mar	Apr	May	Jun	Sum Cols
2	a	1,585983501	1,13879563	5,683209066	7,106206612	7,589446	4,365661269	27,46930208
3	b	5,695317862	3,245678375	8,234361268	2,429451426	1,781528354	1,615018906	23,00135619
4	c	6,882694544	6,703106997	7,830778296	4,10047194	9,902449237	9,264325864	44,68382688
5	d	6,517870227	2,117978311	2,405020782	2,642301856	8,170418657	7,328625978	29,18221581
6	e	2,546614092	5,218132127	9,269729674	8,937018627	6,618198966	6,870403597	39,46009708
Table1

Clearly, you must tweak the M according to your data.

 

[GraH]

a bit shorter and easier

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SetColTypes = Table.TransformColumnTypes(Source,{{"Data", type text}, {"Jan", type number}, {"Feb", type number}, {"Mar", type number}, {"Apr", type number}, {"May", type number}, {"Jun", type number}}),
    AddTotCol = Table.AddColumn(SetColTypes, "Sum Cols", each let
 Rec = Record.SelectFields( _ , {"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}, MissingField.Ignore )
,ColTotal = List.Sum(Record.FieldValues(Rec))
in
 ColTotal)
in
    AddTotCol

 

[albertan]

Hello and thank you for this reply. I use “Get data from folder” so every month incremental data is uploaded for each month. I.e. currently data is available with 2 files (May to June). Next month there will be another file. With July data and so on. I think the code you suggest will give me an error if July to Dec files are not there unless Mussingfield.ignore will take care of it? Is there a way to do it so that it dynamically gets updated with each month’s data as it comes in? Thanks

 

[GraH]

Look at the example given. Jan-Jun data was in, and the code was able to generated the total without the months JUL-DEC being present. Indeed MissingField.Ignore takes care of it.

 

[albertan]

Hello, because I do not have Jan - April columns it's giving an error under SetColTypes.
Is there a way to add ignore statement there or do I need to manually put only periods that are available?

Thanks

 

[GraH]

I'm without Excel to test, however try with deleting that step.