Case study: capacity shortfall
Requested by: Noah Reed at Cedar Repairs, a fictional operations manager.
Identify the team with the largest positive shortage of scheduled hours compared with available hours.
Data: The supplied workbook contains fictional records, including the exceptions described in each task.
Done: Complete the six stages. Keep the original detail until the requested summary, and return the final checked finding. Each stage continues from your previous query.
You have a reporting request rather than a list of isolated commands. Identify the team with the largest positive shortage of scheduled hours compared with available hours. Start by deciding what one output row represents. Keep identifiers available while you clean the detail, then aggregate only after the data has the required types and scope.
The tasks follow the order a colleague would use to prepare this report. Each stage carries your query into the next one, including the steps you chose yourself. A hidden replacement workbook changes the rows and values, so the result must come from repeatable transformations. Treat a missing value as missing unless the task gives a different rule. The final stage returns a checked operational answer that the manager can use.
= Table.Group(Source, {"Team"}, {{"Hours", each List.Sum([Hours]), type number}})
A grouped measure has one row per requested business key.