# SUM subset of columns by row • ### Question

• I have a table like this

PROJECT       NOTES              FTE1             FTE2         FTE3

 Project Notes FTE1 FTE2 FTE3 project1 note1 1 1 1 project2 note2 0 1 1 project3 note3 0 0 1

I want to total the FTE count per project (row) and per FTE (column).  Apologies is this is trivial, I am 1 day into Power Query.

I should add that the quantity of FTE columns is 1...n otherwise I realize I could just

• Edited by Monday, August 28, 2017 3:14 PM
Monday, August 28, 2017 2:31 PM

• Excel 2010/2013/2016 Power Query (aka Get & Transform)
With UnPivotColumns() and Group()
http://www.mediafire.com/file/jn61q639zsvxnle/08_28_17a.xlsx

Monday, August 28, 2017 5:52 PM
• Alternatively you can List.Zip the Record.FieldNames and Record.FieldValues, take the Value if the Name starts with FTE (else 0) and sum the results:

```let
Source = Table1,
#"Added Custom" = Table.AddColumn(Source, "FTECount", each List.Sum(List.Transform(List.Zip({Record.FieldNames(_),Record.FieldValues(_)}), each if Text.Start(_{0},3) = "FTE" then _{1} else 0)))
in

Monday, August 28, 2017 7:19 PM
• Hi Colin,

Yes, it's all about correct and detailed specs. If all FTE columns are at the end and each row needs to be totaled, then indeed I would prefer your last solution.

Just for the record: our previous posts were concurrent; I was not reacting to yours.

Actually, I've had a scenario where I had to sum across 8 columns in the middle of a table. In that scenario, I had to specify columns that begins with "Pane." Although I did it a different way, your solution would have been an option. Another option would be:

```FTECount = Table.AddColumn(Source, "FTE Count", each List.Sum(Record.FieldValues(Record.SelectFields(_, List.FindText(Record.FieldNames(_), "FTE")))))
```

Monday, August 28, 2017 9:18 PM

### All replies

• Excel 2010/2013/2016 Power Query (aka Get & Transform)
With UnPivotColumns() and Group()
http://www.mediafire.com/file/jn61q639zsvxnle/08_28_17a.xlsx

Monday, August 28, 2017 5:52 PM
• Assuming that there are multiple rows per project to group (not just a single row as in your example), you can use the following code, which uses your sample data in an Excel table named Table1.

```let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
SubGroupRows = Table.Group(Source, {"PROJECT"}, {{"Table", each _, type table}}),
AllColumnNames = Table.ColumnNames(Source),
SummedColumnNames = List.Difference(AllColumnNames, {"PROJECT", "NOTES"}),
SummedColumns = List.Transform(List.Positions(SummedColumnNames), (current)=> {SummedColumnNames{current}, each List.Sum(Table.Column(_,SummedColumnNames{current})), type number}),
GroupedSubGroupRows = Table.TransformColumns(SubGroupRows, {"Table", each Table.Group(_, {"PROJECT"}, SummedColumns)}),
CombinedTables = Table.Combine(GroupedSubGroupRows[Table]),
ColumnTotal = Table.AddColumn(CombinedTables, "Column Total", each List.Sum(List.Skip(Record.FieldValues(_))))
in
ColumnTotal```

The code will sum multiple rows per project into a single row per project and then sum each row.

Monday, August 28, 2017 7:15 PM
• Alternatively you can List.Zip the Record.FieldNames and Record.FieldValues, take the Value if the Name starts with FTE (else 0) and sum the results:

```let
Source = Table1,
#"Added Custom" = Table.AddColumn(Source, "FTECount", each List.Sum(List.Transform(List.Zip({Record.FieldNames(_),Record.FieldValues(_)}), each if Text.Start(_{0},3) = "FTE" then _{1} else 0)))
in

Monday, August 28, 2017 7:19 PM
• I will look into this more.  My data is a lot more complicated than the sample table in this question.  Not sure if this is sane but I got row totals (of all the data after "Notes") this way:

let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"FTE1", Int64.Type}, {"FTE2", Int64.Type}, {"FTE3", Int64.Type}}),

Func_IterateRows = (a) => //, rowoffset as number, rowcount as number)
let
tblA = Record.ToTable(a),

//Get the Position of the Term "Notes" as everything following should be variable number of FTE columns
numNotesPositon = (List.PositionOf(tblA[Name],"Notes")+1),

//Get only the fields want to sum
tblSubRange = Table.Range(tblA,numNotesPositon),

//Sum them
numSumSubRange = List.Sum(tblSubRange[Value]),

//This is essentially my message box to change what is returned
ReturnedRow = numSumSubRange
in
ReturnedRow,

in
tblWithRowTotal

Monday, August 28, 2017 7:27 PM
• Alternatively you can List.Zip the Record.FieldNames and Record.FieldValues, take the Value if the Name starts with FTE (else 0) and sum the results:

```let
Source = Table1,
#"Added Custom" = Table.AddColumn(Source, "FTECount", each List.Sum(List.Transform(List.Zip({Record.FieldNames(_),Record.FieldValues(_)}), each if Text.Start(_{0},3) = "FTE" then _{1} else 0)))
in

Hi Marcel,

If only each row needs to be totaled, then I would use a modification of my last step.

`Column Total = Table.AddColumn(Source, "Column Count", each List.Sum(List.Skip(Record.FieldValues(_), 2)))`
However, I interpreted "I want to total the FTE count per project (row) and per FTE (column)" to mean that multiple rows needed to be grouped first.

Monday, August 28, 2017 8:02 PM
• Hi Colin,

Yes, it's all about correct and detailed specs. If all FTE columns are at the end and each row needs to be totaled, then indeed I would prefer your last solution.

Just for the record: our previous posts were concurrent; I was not reacting to yours.

Monday, August 28, 2017 8:14 PM
• "I will look into this more.  My data is a lot more complicated than the sample table in this question."

Would it difficult to provide a more representative sample? If the solutions provided don't address your needs, then it's not clear what you want as a result.

Preferably, a better data sample plus a sample of the final transformation should be provided.

Monday, August 28, 2017 8:15 PM
• Hi Colin,

Yes, it's all about correct and detailed specs. If all FTE columns are at the end and each row needs to be totaled, then indeed I would prefer your last solution.

Just for the record: our previous posts were concurrent; I was not reacting to yours.

Actually, I've had a scenario where I had to sum across 8 columns in the middle of a table. In that scenario, I had to specify columns that begins with "Pane." Although I did it a different way, your solution would have been an option. Another option would be:

```FTECount = Table.AddColumn(Source, "FTE Count", each List.Sum(Record.FieldValues(Record.SelectFields(_, List.FindText(Record.FieldNames(_), "FTE")))))
```

Monday, August 28, 2017 9:18 PM
• Hi DSXBX,