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

[ebachrach]

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

[Anonymous]

Hi @ebachrach,

I think summarize function should suitable for your requirement to summarize records. Can you please share some sample data with detail calculate condition to test and coding formula?

Regards,

Xiaoxin Sheng

[ebachrach]

Hi @Xiaoxin

I have a workbook containing the data on tab PRHIST, and table tblReportSWDepartments on tab Tables. There are some supporting tables as well.  But how do I share the sample?

Thanks Elly

---

@Anonymous wrote:

Hi @ebachrach,

I think summarize function should suitable for your requirement to summarize records. Can you please share some sample data with detail calculate condition to test and coding formula?

Regards,

Xiaoxin Sheng

---

[Anonymous]

HI @ebachrach,

You can upload to onedirve or google drive, then share link here.

Notice: do mask on sensitive data before sharing.

Regards,

Xiaoxin Sheng