none
Sumif in power query RRS feed

  • Question

  • Good morning,

    I have a power query sheet with the following columns: [People]; [Matter]; [hours]. What I need is the total sum of hours work in a matter.

    Example:

    People  Matter   Hours    Total hours

    A            1           5            ???

    B            2           6            ???

    C            1           7            ???

    In this case the total amount of matter 1 is 12 hours. How can I create this new column called "Total hours"?

    Thanks

    Monday, October 5, 2015 9:00 AM

Answers

  • You group on Matter and calculate the total, then you merge this table with your original data-table like a lookup.

        Source = YourTable,
        #"Grouped Rows" = Table.Group(Source, {"Matter"}, {{"TotalHours", each List.Sum([Hours]), type number}}),
        #"Merged Queries" = Table.NestedJoin(Source,{"Matter"},#"Grouped Rows",{"Matter"},"NewColumn",JoinKind.LeftOuter),
        #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"TotalHours"}, {"TotalHours"})

    Trick is to reference two different steps from the same query in the merge(join)-Operation.

    LinkToFile


    Imke Feldmann TheBIccountant.com

    Monday, October 5, 2015 9:23 AM
    Moderator

All replies

  • You group on Matter and calculate the total, then you merge this table with your original data-table like a lookup.

        Source = YourTable,
        #"Grouped Rows" = Table.Group(Source, {"Matter"}, {{"TotalHours", each List.Sum([Hours]), type number}}),
        #"Merged Queries" = Table.NestedJoin(Source,{"Matter"},#"Grouped Rows",{"Matter"},"NewColumn",JoinKind.LeftOuter),
        #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"TotalHours"}, {"TotalHours"})

    Trick is to reference two different steps from the same query in the merge(join)-Operation.

    LinkToFile


    Imke Feldmann TheBIccountant.com

    Monday, October 5, 2015 9:23 AM
    Moderator
  • Hi there,

    thanks for your solution here, it is what i am looking for.

    Just a little question, I took a look at the file, and there is the "Added Index" . Is this necessary or PQ will automatically do it for you?

    Tuesday, April 11, 2017 2:51 PM