excelpowerbipowerquerym

Sum column based on criteria while grouping in Power Query


I am trying to group a datasheet and return the sum of column a based on values in column b but I still need to return the sum of the complete column c so I can't filter the table prior to grouping. I just tried this:

Table.Group (
    MyTable
    , {
        "ColumnD"
    }
    , {
        {
            "Sum Criteria"
            , each List.Sum (
                Table.Column (
                    Table.SelectRows (
                        _
                        , each [ColumnB] = "Foo"
                    )
                    , "ColumnA"
                )
            )
            , Int64.Type
        }
        , {
            "Sum Complete"
            , each List.Sum ( [ColumnC] )
            , type number
        }
    }
)

But the numbers aren't right, in my example the result of the criteria based column should be 15 but it's returning 35.

Can anyone please help me figure out where I am erring?

Thanks


Solution

  • I am really sorry for asking this question, the solution I used initially was right. my mistake was that I previewed the results by adding another column which contained all rows, and while I was previewing it, I counted them out and found that the numbers didn't match. When I expanded the table many more rows were revealed showing that the formula was accurate.

    I wonder what the bug is with the query designer that it didn't show all the rows while previewing the table.

    I am leaving the question so other people can see my formula and enjoy it.