Using Power Query to Import Donation Data Easily from Stewardship

After a short absence we thought we’d offer all you Stewardship users a lifeline that will immediately save you time and reduce errors. We believe passionately in making things easier for Non-Profits; we’re all too aware of the large amounts of time and money ploughed into the storage, protection and systemisation of massive amounts of data, all before you’re even able to benefit from the overall picture that the data can present.

So here’s a question: How does your charity process the donations you receive through Stewardship? We find that many organisations either manually transfer donations received by typing the data into their CRM or manipulate the export file to import the data directly into their CRM. Some may not even have the time for that, making it difficult to know which donor has paid what amount when; this becomes especially complicated and unmanageable if organisations are dealing with child sponsorships or more than 50 regular donations per month.

We’re here to tell you that there’s an easier way: a way that eliminates user error caused by manual data entry, or the manual manipulation of columns in a spreadsheet, and can be done in no more than 5 minutes. Let us introduce you to Microsoft Excel Power Query.

Power Query is a data transformation and data preparation engine. It comes with a graphical interface for getting data from sources and a Power Query Editor for applying transformations. Best of all, it’s free with Excel. For more information on Power Query, see the link to Microsoft here – https://docs.microsoft.com/en-us/power-query/power-query-what-is-power-query

At Innovate NP, we use Power Query to transform the standard Stewardship export file into any kind of import template for your CRM, at the click of a button. For the initial set-up you have to tell Power Query what you want it to do, by following the steps below:

1.Download your Stewardship report and change the tab name to “Export”, then Save with a generic name like “Stewardship Export”.

2.Open a new Excel spreadsheet > Data Tab > Get Data > From File > From Workbook.

3.Navigate to the saved “Stewardship Export” > Import > Select the “Export” tab and Load.

By double-clicking on the query in ‘Queries and Connections’ you can use a number of advanced features to transform and add columns to your file; you’ll also want to delete columns and rows to get the file ready for import. Here are 10 handy commands to help you get started:

https://www.goskills.com/Excel/Resources/Power-Query-tips

Once you have transformed the file, you’ll want it to resemble your import template, so click ‘Close & Load’, and ‘Save As’ “Stewardship Import”.

Now the initial set-up has been done, every time you need to import a new Stewardship report, download the file from Stewardship, rename the tab to “Export” and ‘Save As’ “Stewardship Export” over the original file. Then just open up the “Stewardship Import” file and navigate to Data > Refresh All, and voila, you have your next ready-made import file.

We hope you find this helpful. We’re convinced that simple tricks like this can help to drastically streamline essential daily processes, freeing up time and money capital for investment in bigger and better things.

If you would like to find out more about how we could save your organisation time and money, or would like some more help with adapting spreadsheets with Power Query send us an email at info@innovatenonprofit.com. We look forward to hearing from you.