Create Budget Limits and Totals in Excel Power Query or a PivotTable
Question details
Create budget-limit calculations and totals from unpivoted data using a clear Power Query or PivotTable data model.

- Product
- Microsoft Excel Power Query and PivotTable Data Model
- Device & OS
- not provided
- Scenario
- The user wants to combine budget data, average values, and labels using reliable table relationships.
- Observed behavior
- The desired result is a model with unambiguous relationship keys and totals that use the intended averaging method.
Create a small sample workbook with the source tables, relationship fields, and expected results for a few groups and the grand total. Identify which columns contain budget amounts, labels, and the fields that define each group.
Prepare Unique Keys and Aggregate Budget Data in Power Query
Use this approach to clean and summarize source data before building the PivotTable.
Select each source table and choose Data > From Table/Range to open it in Power Query.
In Power Query, select the columns that need to become attribute-value pairs, then choose Transform > Unpivot Columns. Check that budget amounts and labels remain identifiable.
Use Add Column > Custom Column in each query if the relationship requires a composite key. Combine the same normalized fields in the same order and use a separator or encoding that cannot produce ambiguous keys.
Inspect the lookup query for duplicate or blank keys before merging. Use Home > Remove Rows > Remove Duplicates only when retaining one row per key is appropriate and the associated lookup values are consistent.
Select the fields that define each budget group, then choose Home > Group By and add the required sum, average, or other aggregation. Use Home > Close & Load To to load the result to a PivotTable or the Data Model.

Build a PivotTable Data Model with a Unique Dimension Table
Use a Data Model when separate tables need to filter budget calculations through shared keys.
Review Budget Workbooks with WPS Spreadsheet
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open and edit compatible Excel workbooks for an easy transition, while using Excel for Power Query and Data Model features that WPS may not support.

Frequently Asked Questions
Why does my lookup table need unique keys?
A unique key identifies one lookup row for each related value. Check for duplicates in Power Query before using Data > Relationships > New or merging queries.
How do I relate tables using more than one field?
In each Power Query, use Add Column > Custom Column to create a composite key from the same normalized fields in the same order. Use an unambiguous separator or encoding, and ensure the dimension-side key is unique.
Why is the grand total different from the average of the displayed averages?
The grand total may average all underlying records, giving larger groups more weight. An average of group averages gives each group equal weight; use a measure that matches the intended calculation.
When should I group the data in Power Query?
Use Home > Group By when the PivotTable needs a summarized table at a known group level. Choose the grouping fields and aggregations carefully so that the result retains the detail needed for your calculations.




