Using Power Query to prepare Stewardship donations for CRM import

Charities receiving donations through Stewardship usually end up with a familiar monthly routine: download the export, delete the columns the CRM does not want, rename the ones it does, fix the dates, split a name or two, and paste the result into an import template. It works, it takes an hour or so, and it is slightly different every time depending on who does it.

Power Query, built into Excel, is well suited to exactly this. It records the transformation steps you apply to a source file and reapplies them when the data changes—so the tidying becomes a template rather than a task.

What Power Query does, and what it does not

Power Query is a data connection and transformation tool. You point it at a file, apply steps—remove a column, rename a header, change a type, add a calculation—and it stores those steps in order. Give it a new file with the same structure and it repeats them exactly.

What it does not do is judge whether the result is correct. It will faithfully reformat a duplicated donation, and it will happily convert a supporter reference into a number and lose the leading zero. The value is consistency and saved time, not the removal of checking. Anyone promising a five-minute import has not accounted for the review, which is the part that protects your data.

Start from the file you actually downloaded

Export options and formats change, so do not assume the file will be a particular type. Look at what you have downloaded and connect accordingly:

• An Excel workbook (.xlsx): Data → Get Data → From File → From Workbook, then navigate to the sheet or named table you need.
• A comma-separated file (.csv): Data → Get Data → From File → From Text/CSV.

A consistent sheet or table name matters only where the query navigates to that named item inside a workbook. CSV files have no sheets, so the relevant consistency there is the filename, the delimiter and the column headings.

The process

1. 01Download the donation export and save it in a fixed folder with a consistent filename. Treat that file as read-only.
2. 02Connect to it in a new Excel workbook using the route for its file type, and open it in the Power Query editor rather than loading it straight away.
3. 03Rename, remove and reorder the columns so they match your CRM import template exactly.
4. 04Set the correct data type for every column—text for references and postcodes, date for donation dates, fixed decimal or currency for amounts.
5. 05Add any calculated or standardised values you need: a combined name, a trimmed reference, a fixed campaign code, a consistent payment method label.
6. 06Validate the fields the CRM requires—supporter identifier, donation date and amount—and check for blanks in each.
7. 07Close and load the result into a separate import-ready worksheet, leaving the source untouched.
8. 08Save the workbook as your reusable template, with the query and the import sheet in place.
9. 09Next month, replace or update the source export and refresh the query.
10. 10Review the output before importing it into the CRM.

Getting the data types right

Most import failures come down to types, so this step deserves more care than it usually gets.

• Set references, account numbers and postcodes to text. Excel treats them as numbers by default and strips leading zeros, which cannot be recovered later.
• Set dates explicitly, and where a source uses another region's convention use Change Type Using Locale so 03/07 is not silently read as the wrong gift date.
• Set amounts to fixed decimal number or currency where the option is available, and confirm no currency symbols or thousands separators have come through as text.
• Trim whitespace on anything used for matching. A trailing space is invisible and will fail a lookup.

What to watch for each month

A refreshable query is reliable until the source changes. These are the failures worth checking for deliberately:

• Renamed, removed or added columns, or a change in type or format—any of these can break a step or shift data into the wrong field. Reordering columns does not normally break transformations that refer to headings by name.
• Lost leading zeros in references and postcodes.
• Dates interpreted in the wrong format, especially around the start of a month.
• Duplicate donations—from an overlapping date range, or from running the export twice.
• Missing supporter identifiers, which will either fail to match or create unwanted new records.
• Row counts that do not reconcile with the Stewardship statement total.

For duplicates, use the unique transaction or gift reference from the export where one exists—a supporter can legitimately give several times in a month, so identifier alone proves nothing. If there is no suitable unique reference, flag potential duplicates using a composite of supporter reference, date and amount and investigate them, remembering that genuine repeat gifts can share all three.

Alongside that, compare the row count and total value in your import sheet against the figures on the source statement. Between them, those two checks catch most of what goes wrong.

Test a small import first

Before processing a full file—especially the first time, or after any change to the source—import a handful of rows and check the result in the CRM. Confirm the gift is attached to the right supporter, the date and amount are correct, and no new duplicate record has been created.

Ten minutes here is considerably cheaper than unpicking four hundred incorrectly matched donations, which is slow, manual and visible in your reporting for months.

When to move beyond a spreadsheet

A Power Query template is a good, honest solution for a monthly file processed by someone who understands it. It has limits worth naming: it depends on one person's workbook, it is triggered by hand, and its record of what happened is limited to the applied steps and whatever file or version history you keep. That is not nothing, but it is not an operational run log, alerting or a managed exception process either.

If the volume grows, if acknowledgement timing matters, or if more than one person needs to run it, that is the point to look at a direct integration into the CRM instead. Until then, a documented, refreshable template that produces a consistent result every month is a perfectly respectable piece of engineering—and a large improvement on doing it from memory.