Power Query Problems

Excel Power Query: Build Monthly Project Workloads by Employee Type

Kushani NimanthikaKushani Nimanthika Nov 12, 2026 870 views

Question details

The user needs to project monthly workloads across multiple projects and group the results by employee type.

Excel Power Query: Build Monthly Project Workloads by Employee Type
Product
Microsoft Excel Power Query and PivotTable
Device & OS
not provided
Scenario
Project records contain start dates, end dates, total hours, and employee-type hour columns. The monthly forecast must remain expandable when new projects are added.
Observed behavior
The source data is not yet structured into one row per project, month, and employee type, so a refreshable monthly workload summary is needed.
Before you start

Confirm that each project has valid start and end dates, total hours, and consistently named employee-type columns. Convert the source range to an Excel table and check that date fields are recognized as dates before opening Power Query.

Solution 1Recommended

Use Power Query to create a refreshable monthly workload PivotTable

Use this approach when projects and employee-hour columns will change over time and the forecast must expand with a refresh.

Power Query first converts the wide employee-hour layout into Employee Type and Total Hours rows. It then creates the applicable month rows for each project, allocates hours across those months, and produces a normalized table for reporting.

1
Convert the source to a table

Select the project data range, press Ctrl+T, confirm that My table has headers is selected, and give the table a name such as Projects.

2
Open the table in Power Query

In Excel, select Data > From Table/Range to open the Projects table in Power Query Editor.

3
Unpivot employee-hour columns

Select the project identifier, start date, end date, and other identifying columns, then choose Transform > Unpivot Columns > Unpivot Other Columns. Rename Attribute to Employee Type and Value to Total Hours.

4
Generate monthly project rows

Add a custom Power Query column that creates a list of month-start dates from each project start month through its end month, then expand the list into rows. Add Year and Month columns from the generated date.

5
Allocate hours and load the report

Calculate the monthly allocation from Total Hours and the number of applicable months, select Home > Close & Load To, and load the result to a worksheet. Insert a PivotTable with Year and Month in Rows, Employee Type in Columns, and allocated hours in Values. Refresh the query and PivotTable after adding projects.

Keep the forecast expandable: Add new projects inside the Projects table, then use Data > Refresh All. The query and PivotTable will include the new records without rebuilding the report.

Build a Refreshable Monthly Workload Report in WPS Spreadsheet

WPS Spreadsheet can organize project data in an Excel-compatible table, expand the project-month records with formulas, and summarize employee-type workloads in a PivotTable. This provides a lightweight workflow for maintaining monthly forecasts as projects are added.

  1. 1. Format the project source: Open WPS Spreadsheet, select the project range, and choose Insert > Table. Confirm the header row and include project dates, total hours, and employee-type hour columns.
  2. 2. Build the monthly detail table: Create a worksheet with one row per project and month. Use formulas such as EOMONTH and EDATE to generate month-start values between each project's start and end dates.
  3. 3. Calculate employee-type allocations: Add Employee Type and Monthly Hours columns, then use formulas to divide each employee type's project hours across the applicable months or apply your chosen allocation rule.
  4. 4. Create and refresh the summary: Select the normalized detail table and choose Insert > PivotTable. Put Year and Month in Rows, Employee Type in Columns, and Monthly Hours in Values; refresh the PivotTable after adding projects.
Create monthly workload summaries by project and employee type with spreadsheet formulas and PivotTables.Use a familiar table-based workflow without rebuilding the source range for every new project.Open, edit, and share Microsoft Excel XLSX and XLS files with broad format compatibility.Keep project dates, hours, and employee classifications organized in one structured worksheet.
microsoft office alternative - wps office

Frequently Asked Questions

How should employee-hour columns be structured before using Power Query?

Keep project identifiers, start date, and end date as identifying columns, and place each employee type in its own hour column. In Power Query, select the identifying columns and choose Transform > Unpivot Columns > Unpivot Other Columns.

How can I make the monthly forecast include new projects automatically?

Store the source records in an Excel table rather than a fixed range. Add new projects directly below the table, then select Data > Refresh All so Power Query and the PivotTable include them.

How should hours be allocated across project months?

For an even allocation, divide each employee type's total hours by the number of applicable months. If workload follows working days or a custom schedule, calculate a monthly weighting column and multiply the total hours by each month's weight.

What fields should be placed in the workload PivotTable?

Place Year and Month in Rows, Employee Type in Columns, and the allocated monthly hours field in Values. Add Project or other identifiers to Filters when you need to inspect individual projects.