Power Query Problems

Create Budget Limits and Totals in Excel Power Query or a PivotTable

Chanuka GeekiyanageChanuka Geekiyanage Nov 18, 2026 870 views

Question details

Create budget-limit calculations and totals from unpivoted data using a clear Power Query or PivotTable data model.

Create Budget Limits and Totals in Excel Power Query or a PivotTable
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.
Before you start

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.

Solution 1Recommended

Prepare Unique Keys and Aggregate Budget Data in Power Query

Use this approach to clean and summarize source data before building the PivotTable.

1
Load the source tables

Select each source table and choose Data > From Table/Range to open it in Power Query.

2
Unpivot the relevant columns

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.

3
Create consistent relationship keys

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.

4
Check the lookup table

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.

5
Group and load the data

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.

Prepare Unique Keys and Aggregate Budget Data in Power Query
Choose the total deliberately: A grand total of averages may need to be weighted by the number of underlying records or calculated as an equal-weight average of group averages. These are different results, so compare the total with the expected result in the sample workbook.
Free Microsoft Office alternative

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.

Review budget tables and totals in a familiar spreadsheet interfaceCompatible with Microsoft Excel XLSX and XLS filesFree and lightweight for everyday spreadsheet workOpen existing workbooks without starting your budget sheets from scratch
microsoft office alternative - wps office

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.