Power Query Problems

How to Combine Matching Debit and Credit Rows in Power Query

Guest Writer Oct 9, 2026 871 views

Question details

The user needs to group rows based on matching identifiers (company, location, department, account, line description) and combine their respective debit and credit amounts into separate output columns.

How to Combine Matching Debit and Credit Rows in Power Query

Product: Microsoft Excel
Device & OS: not provided
Scenario: Consolidating financial data where multiple transactions for the same account and department are spread across several rows, requiring aggregation into total debits and credits per unique identifier.
Observed behavior: Financial records are currently split into individual transaction rows, and the goal is to output a summarized view showing total debit and credit amounts grouped by matching key values.

Before you start

Ensure your financial dataset is formatted as an Excel Table (Ctrl+T) and that your column headers, especially 'Debit' and 'Credit', are clearly labeled before importing into Power Query.

Solution 1Recommended

Use Unpivot and Pivot with Sum Aggregation

This method transforms the layout by temporarily unpivoting the financial columns, grouping the exact matches, and pivoting them back with a sum aggregation.

By utilizing the Table.UnpivotOtherColumns and Table.Pivot functions, Power Query can dynamically group multiple matching text identifiers and sum the numerical values accurately.

Load Data into Power Query

Select your data table, navigate to the Data tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.

Select Key Identifier Columns

Hold the Ctrl key and select the columns that make up your matching criteria (e.g., Company, Location, Department, Account, Line Description).

Unpivot Other Columns

Right-click one of the selected headers and choose 'Unpivot Other Columns'. This will collapse your Debit and Credit columns into an 'Attribute' column and a 'Value' column.

Pivot the Attribute Column

Select the new 'Attribute' column. Go to the Transform tab and click 'Pivot Column'.

Apply Sum Aggregation

In the Pivot Column dialog, choose 'Value' as the Values Column. Expand the Advanced Options, ensure 'Sum' is selected as the Aggregate Value Function, and click OK. Finally, click 'Close & Load' to return the combined data to your spreadsheet.

Use Unpivot and Pivot with Sum Aggregation

Advanced Editor M Code: For advanced users, this translates to the M query step: Table.Pivot(UnpivotedTable, List.Distinct(UnpivotedTable[Attribute]), "Attribute", "Value", List.Sum).

Solution 2

Use the Group By Feature Directly

An alternative native UI approach to achieve the same result without unpivoting columns.

Easy Data Consolidation

Use WPS Spreadsheet to Combine and Sum Data Instantly

If you want to avoid writing complex Power Query M code, you can effortlessly group matching financial identifiers and sum your Debit and Credit columns using the intuitive PivotTable feature in WPS Spreadsheet.

1. Open Your Data: Launch WPS Spreadsheet and open the .xlsx file containing your transaction rows.
2. Insert a PivotTable: Highlight your entire data range, navigate to the 'Insert' tab, and click 'PivotTable'.
3. Group Matching Rows: In the PivotTable fields pane, drag your key identifiers (Company, Location, Department, Account) into the 'Rows' area.
4. Sum Debits and Credits: Drag the 'Debit' and 'Credit' fields into the 'Values' area.
5. Verify Aggregation: Ensure the value field settings are configured to 'Sum' so that WPS automatically calculates the combined totals for each grouped row.

Create complex data summaries and aggregations without writing any code.Highly compatible with Microsoft Excel (.xlsx) file formats and data structures.Lightweight software with fast performance, even when processing large financial datasets.Familiar user interface makes transitioning and data formatting seamless.

microsoft office alternative - wps office

Frequently Asked Questions

Why are my Debit and Credit amounts not summing correctly when pivoting?

This typically occurs if the data type of your Value column is set to Text or Any instead of a numeric type. Ensure you change the data type to Whole Number or Decimal Number in the Transform tab before executing the Pivot operation.

How does Power Query handle null values when combining these rows?

Power Query's unpivot function automatically drops null values. If you need nulls to be represented as zeros in your final output, replace the nulls with '0' in your Debit and Credit columns before performing the unpivot step.

Can I group rows by multiple columns in Power Query without unpivoting?

Yes, you can use the 'Group By' feature on the Transform tab. By choosing the 'Advanced' option, you can specify multiple columns to group by and create separate aggregations for the Sum of Debit and Sum of Credit.

Does WPS Spreadsheet support Power Query M code?

WPS Spreadsheet currently focuses on high-performance data analysis tools like PivotTables, advanced filtering, and functions rather than executing Power Query M code. However, it is fully compatible with viewing and editing the resulting .xlsx datasets.

Excel & Data Problems