Spreadsheets

Power Query: unpivot debit and credit columns into one amount column

Use Power Query to unpivot debit and credit columns into Type and Amount, then add a signed amount column that refreshes when new months arrive.

Editorial illustration for Power Query: unpivot debit and credit columns into one amount column

To fold separate debit and credit columns into one, select the columns that are not amounts, right-click and choose Unpivot Other Columns. Power Query returns two new columns, Attribute and Value. Rename them Type and Amount, then add a conditional column that makes debits negative. Refresh brings in each new month.

What does unpivot actually do?

Your statement has one row per transaction with Withdrawal and Deposit side by side. Unpivoting turns those two columns into rows: the heading becomes the Type, and the figure becomes the Amount. A row that had a figure in only one of the two columns becomes a single new row, which is exactly what you want.

DateNarrationWithdrawalDeposit
03-NovRent18,000blank
04-NovSalaryblank52,000
09-NovRefundblank1,299

After unpivoting Withdrawal and Deposit you get Date, Narration, Type and Amount: 03-Nov, Rent, Withdrawal, 18,000; then 04-Nov, Salary, Deposit, 52,000; and so on.

How do you do it step by step?

  1. Select the data and choose Data, From Table/Range.
  2. Make sure the amount columns are numbers; if they are text, clean and convert them first.
  3. Select the Date and Narration columns, and any Balance column, with Ctrl-click.
  4. Right-click one heading and choose Unpivot Other Columns.
  5. Rename Attribute to Type and Value to Amount.
  6. Check for rows with 0 or empty text that survived; blank cells normally disappear in the unpivot, but zeros and spaces stay, so filter them out if they should not count.

How do you turn Type and Amount into a signed amount?

The Conditional Column dialog can return only a fixed value or an existing column, not a negated one, so use Add Column, Custom Column with this formula: if [Type] = "Withdrawal" then -[Amount] else [Amount]. Name the column SignedAmount.

Then remove the old Amount column, or keep it for reference, and set SignedAmount to Decimal Number. Close and Load puts the table in a new sheet, with money out negative and money in positive.

Why is this good when new months keep arriving?

Power Query remembers the steps. Paste next month's rows into the source table, click Data, Refresh All and the same unpivot and conversion run again. Because you chose Unpivot Other Columns rather than naming each amount column, an extra amount column, for example a charges column, would also be unpivoted automatically. That is handy when it is intended and a surprise when it is not, so look at the Type values after a refresh.

Does anything go wrong?

  • Column not found: the bank renamed a heading that a step refers to. Open Applied Steps and update the name.
  • Unexpected type: new rows contain a figure with a comma stored as text. Add the cleaning steps before the unpivot.
  • A single row with both columns filled becomes two rows, one for each side, which is correct but looks odd.
  • Totals that differ from the statement: compare SUM(SignedAmount) with total credits minus total debits.

Worked example with hypothetical numbers

Take the three rows above plus a groceries withdrawal of 2,350. Signed amounts are -18,000, 52,000, 1,299 and -2,350. Their sum is 32,949. Total deposits 53,299 minus total withdrawals 20,350 also gives 32,949, so the unpivot has not lost or doubled a row.

Closing checklist

  • Amounts numeric before unpivoting.
  • Unpivot Other Columns, then rename to Type and Amount.
  • Custom column for the signed value.
  • Compare the total with the statement, then refresh monthly.

If maintaining queries is not how you want to spend an evening, SpendixAI lets you upload statements as PDF, CSV or Excel and review rows before saving, with no unpivot step on your side.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports