# Sumif in power query

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

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

Imke Feldmann TheBIccountant.com

Monday, October 5, 2015 9:23 AM

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