Query that creates new rows which sum certain fields based on external grouping

Hi,

New to Power Query, I have searched but cannot determine how to accomplish my need.

I have a table containing payroll data categorized by facility, payroll date, and department. Other fields contain Hours worked, OT hours, Total Hours, Total Pay and similar values.  For review purposes, those departments are actually grouped based on "reporting departments"  For example, Departments 23, 24, 25, and 26 are 4 kinds of nursing staff, but for reporting they are grouped as "Direct Care". 

I need to create a new table that for every facility and payroll date in the original, contains rows with the information for each "reporting department", with the values of the other fields summed.  **There are some fields, like "Census" which are not to be summed and should the value should be used without any aggregation.

The attached images show my original data and the secondary table identify which departments are in which reporting departments.

I have been able to do this using measures, but I need to do more operations on the data so i need a table to work with.

Any guidance would be greatly appreciated.

Elly