Sharing my Power BI Template: Combine Queries for Hard/Soft Credit Recognition

Rebecca Sundquist

This template addresses how to combine Blackbaud's Raiser's Edge NXT Gifts with Soft Credit records for a total view of constituent engagement.

• Required Connections: sample .csv files pulled from RENXT Query are included
• Skill Level: beginner
• Category: Power Query Editor

For Blackbaud's Power BI Accelerator workshop attendees, I found an issue in Lab 1a bonus content where we used a Merge to combine hard-credit Gift records with Soft Credit records for a total view of constituent engagement. I am attaching updated documentation and a sample workbook.

Originally, the join to merge Soft Credit records with hard credit Gift records was linking the two table on Gift Record ID. This meant that when a Gift Record ID existed in both the Soft Credit table and the Gift table, then the merged result would keep the soft credit information for the newly merged record and drop the hard credit activity from the gift record. The desired behavior is to keep the hard credit for the donor and the soft credit for the second constituent.

First, I explored using Append to combine Soft Credits + Gift records. But, for the purpose of our workshop content, I want to avoid the extra steps to get Gift and Soft Credit fields identically named so that the append stacks the records into appropriate columns. Also note, if you try the Append method, you may need to remove duplicates to avoid double counting cases where the Gift donor also appears with a Soft Credit record for the same gift.

Finally, I adjusted the Merge rules to join on the unique combination of Constituent ID + Gift ID so that when the constituent on the soft credit is identical to the gift's donor, then the Soft Credit and Gift fields are all available; but when the constituent on the soft credit is different from the gift's donor, then all Soft Credit fields are available to track that credit. I used a full outer join to also pick up gifts that have no soft credits at all. In this way, we can differentiate "hard" vs "soft" recognition credit types.

Refer to Tutorial: Shape and combine data in Power BI Desktop - Power BI | Microsoft Learn

Download the attached documentation and sample workbook. Sample data is for the Sky Developer Cohort environment (Raiser's Edge NXT).

Combine_Queries.zip

Shout out to @Yessenia Bernabet Claros and @Cole Welsh for struggling through the original content!