none
SUM subset of columns by row RRS feed

  • 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.

    Thanks in advance.


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

    Table.AddColumn(Source,"Count", each [FTE1]+[FTE2]+[FTE3]

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

Answers

  • 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
        #"Added Custom"

    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
        #"Added Custom"

    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,
          
        tblWithRowTotal = Table.AddColumn(Source,"ResourcesPerProject", each Func_IterateRows(_))
    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
        #"Added Custom"

    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,

    if any of the solutions given solved your problem, please mark it as answer. Otherwise please give more details.

    Thx.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Monday, September 4, 2017 5:28 AM
    Moderator