Power Query Problems

Microsoft Excel Power Query: Calculate Total Amount for Each Customer

Jason MillerJason Miller Nov 12, 2026 871 views

Question details

The user needs to calculate the total amount associated with each customer from source records.

Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
Customer transaction data contains multiple records per customer, and the user wants a grouped total for reporting.
Observed behavior
The desired result is one summarized row per customer with the Amount values added together.
Before you start

Confirm that the source table has a consistent Customer column and a numeric Amount column. Load the data into Power Query and check that Amount is assigned a numeric data type before grouping.

Solution 1Recommended

Group records by Customer and sum the Amount column in Power Query

Use this method when each customer appears in multiple source rows and you need one aggregated total per customer.

Power Query's Group By command combines rows with the same customer value and applies an aggregation such as Sum to the Amount field. You can add calculated columns after the grouping step if the report needs additional metrics.

1
Open the source query

In Excel, select a cell in the source table, open the Data tab, and choose From Table/Range to launch Power Query Editor.

2
Open Group By

In Power Query Editor, select the Customer column, open the Home tab, and click Group By.

3
Configure the customer total

In the Group By dialog box, set Group by to Customer, choose Sum under Operation, select Amount as the column to aggregate, and enter a name such as Total Amount.

4
Apply the query

Click OK to create the grouped results, review the customer totals, and choose Home > Close & Load to return the result to Excel.

5
Add an optional share calculation

If needed, open Add Column > Custom Column and create a Share expression using each customer's Total Amount divided by the overall total.

Group records by Customer and sum the Amount column in Power Query
Check the Amount data type: If the Sum option is unavailable or produces unexpected results, select Amount and use Transform > Data Type > Decimal Number or Whole Number before grouping.
Free Microsoft Office alternative

Use WPS Spreadsheets to summarize customer amounts in a familiar worksheet

WPS Office is a free, lightweight Microsoft Office alternative for organizing customer records, calculating totals, and creating reports. Although WPS does not run Microsoft Power Query, you can open compatible files, use spreadsheet formulas and PivotTables, and migrate workflows with a familiar interface.

  1. 1. Open the source data: Launch WPS Spreadsheets, choose Open, and select the XLSX or CSV file containing the Customer and Amount columns.
  2. 2. Create a customer summary: Use Insert > PivotTable, place Customer in the Rows area, and place Amount in the Values area with the value setting configured as Sum.
  3. 3. Save in an Office-compatible format: Choose Menu > Save As and select Excel Workbook (*.xlsx) when the summarized report needs to be shared with Microsoft Excel users.
Create customer totals with SUMIF, PivotTables, and spreadsheet formulasOpen and edit Microsoft Excel XLSX files with strong format compatibilityImport CSV data and organize source records in a lightweight spreadsheet environmentUse a familiar Office-style interface for easier migration from Excel
microsoft office alternative - wps office

Frequently Asked Questions

Why can I not select Sum in the Power Query Group By dialog?

The Amount column is usually stored as text. In Power Query Editor, select Amount, choose Transform > Data Type, and change it to Whole Number, Decimal Number, or another numeric type before opening Group By.

How do I group customer totals when customer names have inconsistent spelling?

Clean the Customer column before grouping. Use Transform > Format > Trim to remove extra spaces and apply Replace Values or a standardization step to correct spelling differences.

Can Power Query calculate a percentage share for each customer?

Yes. After creating Total Amount with Group By, use Add Column > Custom Column to divide each customer's total by the overall total. Make sure the denominator is numeric and format the result as a percentage.

Should I use Power Query or Power Pivot for customer totals?

Use Power Query for repeatable data cleaning and summarization during the import process. Use Power Pivot when totals must remain dynamic in a data model and respond to relationships, filters, or PivotTable selections.