Unify Nonprofit Constituent Data

Build a deduplicated donor master list with automation and a live dashboard.

Introduction

30 Second Summary

Every nonprofit sends thank-you emails, tracks donations, and manages event registrations. But when the same donor shows up as "Dr. Robert Smith" in one system, "Bob Smith" in another, and just an email address in a third, the organization cannot answer a basic question: how many supporters do we actually have?

In this project, you will unify messy donor data from three platforms into a single clean record per person, automate ongoing duplicate detection, and build a dashboard that proves the impact to leadership.

What You'll Build

When you finish, you will open a live dashboard in your browser and walk through four pages showing leadership exactly what changed: duplicates eliminated, donors reclassified to higher giving levels, and an automated system keeping the data clean going forward.

By the end of this project, you'll have:

  • A unified data model and clean master constituent list that transform three systems' worth of messy donor data into one golden record per person, with every merge decision documented in a stakeholder-ready audit trail.
  • An automated duplicate detection system that catches new duplicates the moment they arrive and routes them for human review, so your clean data stays clean.
  • A four-page interactive dashboard hosted as a live portfolio link, proving impact across data quality, fundraising outcomes, and operational health.
  • An optional Secret Mission to push your skills further.

Are there any prerequisites?

You need a Microsoft 365 account with access to SharePoint and Power Automate, Power BI Desktop installed on your computer, a GitHub account, and familiarity with Excel.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters.

You're going to take messy, fragmented donor data from three different nonprofit systems and turn it into a clean, unified dataset with automated protection and a leadership-ready dashboard. This is a portfolio piece you can present as a business case in any Senior Director interview.

Set Up Your Project and Generate the Data

Every data unification project starts with the same challenge: messy, fragmented records scattered across multiple systems. The Parkinson's Foundation stores donor data in three different platforms, and each one captures people differently.

In this step, you will create your project workspace and generate three realistic CSV files that simulate exports from those systems. These files are intentionally messy, packed with the duplicates, inconsistencies, and gaps that make enterprise data operations necessary.

In this step, get ready to:
  • Create your project folder and generate three messy CSV datasets using AI.
  • Verify access to Power BI Desktop, Power Automate, and SharePoint.
  • Review the raw data in Excel to understand the fragmentation you are solving.
Create your project folder and generate the datasets

You need raw materials before you can assess the problem. The three files you generate here simulate real data exports from the Foundation's CRM, online fundraising tool, and email marketing system.

  • Press the Windows key to open your search bar.
  • Type PowerShell and press Enter to open it.
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir Enterprise-Data-Unification
cd Enterprise-Data-Unification
  • Confirm the folder exists by running:
ls

The folder is empty for now. You should see no files listed.

Now you will use AI to generate three messy CSV files that simulate exports from the Foundation's donor systems.

  • Open your browser and navigate to claude.ai or chatgpt.com.
  • Start a new chat and paste the following dataset generation prompt:
Generate three CSV files that simulate messy donor data exports from a nonprofit called the Parkinson's Foundation. Include intentional duplicates, inconsistencies, and data gaps across all files.

FILE 1: raiser_edge_export.csv (exactly 75 rows of data, plus header)
Columns: constituent_id, prefix, first_name, last_name, suffix, email, phone, street_address, city, state, zip, total_giving, last_gift_date, last_gift_amount, donor_status
- Use formal names with titles (Dr., Mr., Mrs.) and suffixes (Jr., III)
- Staff-verified data with mostly clean names but some inconsistent capitalization
- Include 5-8 internal duplicates (same person, slightly different records)
- Some rows have missing phone numbers or addresses

FILE 2: fundraise_up_export.csv (exactly 100 rows of data, plus header)
Columns: transaction_id, donor_name, donor_email, amount, date, campaign, recurring, address, city, state, zip
- Self-entered names in a single donor_name column with inconsistent formatting
- Mix of "First Last", "FIRST LAST", "first last", and names with typos
- Include 8-10 internal duplicates
- Some missing addresses

FILE 3: hubspot_export.csv (exactly 90 rows of data, plus header)
Columns: contact_id, email, firstname, lastname, company, phone, city, state, zip, lifecycle_stage, last_email_open, email_sends, email_opens, email_clicks
- Email-first records where some contacts have no first or last name at all
- Include 5-7 internal duplicates
- Some lifecycle_stage values are inconsistent (lead vs Lead vs LEAD)

What does this prompt do?

This prompt asks the AI to generate three files that mirror how real nonprofit systems store people differently.

  • raiser_edge_export.csv simulates a CRM where staff enter formal names with titles like Dr. and Mrs.
  • fundraise_up_export.csv simulates an online donation form where donors type their own names however they please.
  • hubspot_export.csv simulates an email marketing tool that captures emails first and sometimes never gets a name at all.

Each file includes deliberate internal duplicates and inconsistencies. That is the entire point.

The AI needs to know one more critical detail: which people appear across multiple systems. This cross-system overlap is what makes deduplication necessary.

  • In the same chat, paste this follow-up to add cross-system overlap requirements:
CROSS-SYSTEM OVERLAP (critical):
- 25 people must appear in ALL THREE files with different name formats and sometimes different email addresses
- 15 people must appear in exactly TWO files
- Remaining records are unique to one file

Output each file as complete, copy-ready CSV content with headers. Separate each file clearly with the filename.

Why does cross-system overlap matter?

In a real organization, the same donor exists in multiple systems under slightly different records. One person might be "Dr. Jane Smith" in the CRM, "jane smith" on a donation form, and just "jsmith@gmail.com" in the email tool.

The 25 people who appear across all three files are the hardest to match. Finding them is the core challenge of data unification.

  • Wait for the AI to generate all three CSV outputs.
  • For each file, copy the full CSV content from the AI's response.
  • Open Notepad (press the Windows key, type Notepad, press Enter).
  • Paste the first CSV content and click File, then Save As.
  • Navigate to your Enterprise-Data-Unification folder on the Desktop.
  • Change the Save as type dropdown to All Files (*.*) and save as raiser_edge_export.csv.
  • Repeat for the second file, saving it as fundraise_up_export.csv.
  • Repeat for the third file, saving it as hubspot_export.csv.

Some AI tools offer a copy button

Both Claude and ChatGPT display code or CSV output in formatted blocks with a copy button in the top-right corner. Use it to grab the full CSV content without accidentally missing rows.

  • Switch back to your PowerShell window and confirm all three files exist by running:
ls

You should see raiser_edge_export.csv, fundraise_up_export.csv, and hubspot_export.csv listed in the output.

Don't see all three files?

  • Make sure you saved each file into the Enterprise-Data-Unification folder on your Desktop, not your Documents or Downloads folder.
  • Check that you changed Save as type to All Files (*.*) before saving. Without this, Notepad may have appended .txt to the filename.
  • If the AI did not generate all three files, scroll back through the chat and look for any it may have cut short. Ask it to regenerate any missing file.

Still stuck? Help me troubleshoot my missing CSV files in the Enterprise-Data-Unification folder.

Verify your tool access

Before going further, confirm that the three tools you will use throughout this project are accessible.

  • Press the Windows key to open your search bar.
  • Type Power BI Desktop and press Enter to open it.
  • Confirm you see the Power BI Desktop start screen with options to open a report or get data.
  • Close Power BI Desktop for now. You will use it in a later step.
  • Open your browser and navigate to make.powerautomate.com.
  • Sign in with your Microsoft 365 account when prompted.
  • Confirm you see the Power Automate home page with options to create flows.
  • In a new browser tab, navigate to your organization's SharePoint site.
  • Confirm you can see your SharePoint home or a team site. You will create lists here in a later step.

Cannot sign in to Power Automate or SharePoint?

  • Both tools require a Microsoft 365 work or school account. Personal Microsoft accounts (like Outlook.com) do not have access to Power Automate cloud flows or SharePoint.
  • If your organization uses single sign-on, try signing in with the same credentials you use for Outlook or Teams.

Need more help? Help me troubleshoot my Microsoft 365 sign-in for Power Automate and SharePoint.

Review the raw data in Excel

Now that your files are saved, open them side by side to see the mess you are solving. This is your first audit of the data.

  • Open File Explorer and navigate to your Enterprise-Data-Unification folder on the Desktop.
  • Double-click raiser_edge_export.csv to open it in Excel.
  • Scroll down to the last row and note the total number of data rows (should be 75).
  • Go back to File Explorer and double-click fundraise_up_export.csv to open it. Confirm it has 100 rows of data.
  • Open hubspot_export.csv the same way. Confirm it has 90 rows of data.

Quick row count in Excel

Press Ctrl+End to jump to the last cell with data. The row number in the bottom-left corner tells you the total rows. Subtract 1 for the header row to get the count of data records.

Before you start comparing the files, take a moment to predict: which file do you think will be the messiest, and why?

  • Look at the name columns across all three files and spot the differences:
  • Raiser's Edge has formal names with separate prefix, first_name, last_name, and suffix columns. Staff entered these, so formatting is mostly consistent.
  • Fundraise Up has a single donor_name column where donors typed their own names. You will see a mix of "Jane Smith", "JANE SMITH", and "jane smith" formats.
  • HubSpot has firstname and lastname columns, but some rows are completely blank because the contact came in as an email address only.

Why do these systems store people so differently?

Each system was built for a different team with different priorities. The CRM serves fundraising staff who carefully enter verified donor records. The online donation form captures whatever the donor types. The email marketing tool only cares about having a valid email address.

This fragmentation is not a bug. It is the natural result of organizations adopting tools over time without a unified data strategy. That is exactly what you are building.

Spot-check a few records across the files. Look for names that look like the same person but appear differently in each system. These cross-system duplicates are the core problem you will solve.

Row counts do not match the expected numbers?

  • AI-generated data is not always exact. If your row counts are close (within 5-10 rows) to the targets of 75, 100, and 90, that is fine for this project.
  • If a file is drastically short (e.g., 20 rows instead of 75), the AI may have truncated its output. Go back to the chat and ask it to regenerate that specific file.
  • Make sure you are not counting the header row as a data row. The header is row 1 in Excel, so 75 data rows means the last row is row 76.

Still stuck? Help me verify my AI-generated CSV files have the right number of rows.

You have your raw materials and you have seen the mess firsthand. Three systems, three different ways of storing the same people, with duplicates hiding everywhere. Next up, you will audit this data systematically and design a unified model that defines what a clean record looks like.

Audit the Data and Design the Unified Model

Your project folder is loaded with three messy CSV files, and you have already seen the surface-level chaos: titles in one file, missing names in another, inconsistent formats everywhere. Now it is time to go deeper.

You cannot fix what you do not understand. Before building any solution, you need to document exactly how each system stores people differently. Then you will define what a clean, unified constituent record looks like across the entire Foundation.

In this step, get ready to:
  • Audit the three source files side by side to document overlapping fields, unique fields, and data quality gaps.
  • Generate a unified data model blueprint using AI, producing an Excel workbook with four defined tabs.
  • Review the Authority Rules that define which system wins when data conflicts arise.
Examine the three source files side by side

Each of the Foundation's three systems stores people in its own way. Raiser's Edge uses formal, staff-verified names with titles and suffixes. Fundraise Up stores self-entered donor names with inconsistent formatting. HubSpot leads with email addresses and sometimes has no name at all. Seeing these patterns firsthand is the foundation for every decision you make next.

  • Switch back to Excel where you have the three CSV files from the previous step.
  • Open all three files so you can compare them: raiser_edge_export.csv, fundraise_up_export.csv, and hubspot_export.csv.
  • Look at the column headers across all three files. Note which fields appear in every system (like email or first name), which are unique to one system (like a donor title or email engagement score), and where naming conventions differ.
  • Scan down each column for blank cells. Note which fields are commonly empty in each file.
  • Look for the same person appearing across files. Try searching for a common email address in all three. Notice how the same donor can have completely different name formats depending on the system.

What patterns should I notice?

Raiser's Edge records might show "Dr. Robert Smith III" while Fundraise Up shows "bob smith" and HubSpot just has the email with no name at all. These are the exact inconsistencies your unified model will resolve.

You should also see internal duplicates within each file, not just across files. A single system can have the same person listed more than once with slightly different data.

You now have a firsthand sense of the fragmentation. The overlapping fields, the gaps, and the naming chaos are all inputs for the data model you are about to design.

Generate the unified data model blueprint

Your audit documented the mess. Now you need a blueprint that defines what clean, unified data looks like. This blueprint will be an Excel workbook with four tabs, each serving a specific role in the data model.

  • Open Claude or ChatGPT in your browser.
  • Upload all three CSV files to the conversation (raiser_edge_export.csv, fundraise_up_export.csv, and hubspot_export.csv).
  • Paste the following prompt to generate the data model blueprint:
I have three CSV files from a nonprofit organization's data systems:
- Raiser's Edge (CRM with staff-verified donor records)
- Fundraise Up (online fundraising with self-entered donor data)
- HubSpot (email marketing contacts)

I've attached all three files. Create a unified data model blueprint as a downloadable Excel workbook with four tabs:

Tab 1 - Source Field Inventory: Map every field from all three systems side by side. Show which fields exist in each system and where they overlap or differ.

Tab 2 - Master Field List: Define the golden record structure for a unified constituent. Each row is a field in the master record with its data type, description, and whether it is required.

Tab 3 - Field Mapping: Map each source system field to its corresponding master field. Include transformation notes where formatting or combining is needed.

Tab 4 - Authority Rules: For every master field, define which source system wins when systems have conflicting data. Include plain-language reasoning for each decision. Apply these principles:
- CRM (Raiser's Edge) is the authority for biographical data because records are staff-verified
- The most recently used email wins for communication fields
- Giving history aggregates totals across all sources
- When a field exists in only one system, that system is the default authority

What does each tab produce?

  • Source Field Inventory lays out every column from all three files so you can see the full scope of your data landscape at a glance.
  • Master Field List defines the golden record: the single, unified structure that every constituent will follow after deduplication.
  • Field Mapping connects each source field to its master field, including notes on how to transform data (like splitting a full name into first and last).
  • Authority Rules is the decision-making layer. It defines which system wins when two sources disagree about the same person.
  • Download the Excel workbook the AI generates.
  • Save it to your Enterprise-Data-Unification project folder.
  • Open the workbook in Excel and click through the tabs at the bottom. You should see all four tabs: Source Field Inventory, Master Field List, Field Mapping, and Authority Rules.

AI didn't produce an Excel file?

Some AI tools may output a table in text instead of a downloadable file. If this happens, ask the AI directly: "Please generate this as a downloadable .xlsx Excel file with the four tabs as separate sheets." Both Claude and ChatGPT can produce downloadable Excel files when asked explicitly.

If the file opens but tabs are missing, check whether the AI combined everything into one sheet. Re-prompt and emphasize that each tab must be a separate sheet in the workbook.

Still stuck? Help me get the AI to generate a four-tab Excel workbook for my data model blueprint.

Review the Authority Rules tab

The Authority Rules tab is the most important deliverable in the entire blueprint. It defines every conflict resolution decision your organization would follow when two systems disagree about the same person. In a real organization, this is the document you would present to stakeholders for approval before touching any data.

  • Click the Authority Rules tab at the bottom of the workbook.
  • Read through each row. Every master field should have a defined winner and a plain-language explanation of why that system is the authority.

Before you review the specific rules, think about this: if Raiser's Edge says a donor's name is "Dr. Robert Smith III" and Fundraise Up says "bob smith", which one should your unified record keep? Why?

The answer is Raiser's Edge. Its records are staff-verified, making them the most trustworthy source for biographical data. That principle drives the entire Authority Rules tab.

What should the Authority Rules define?

The Authority Rules tab applies three core principles across every field in the master record:

  • CRM (Raiser's Edge) is the authority for biographical data like name, title, and suffix. Staff enter and verify this information, making it more reliable than self-entered data.
  • Most recently used email wins for communication fields. A donor's current email matters more than an old one, regardless of which system stored it first.
  • Giving history aggregates across all sources. Total giving is the sum of donations from every system, not a pick-one decision. A donor who gave $500 through Fundraise Up and $200 through Raiser's Edge has a true total of $700.
  • Confirm that every field in the Authority Rules tab has a defined winner. No field should be blank in the "Authority Source" or "Reasoning" columns.
  • Spot-check the reasoning. If any rule does not follow the three principles above (CRM for biographical, most recent for email, aggregate for giving), adjust it or re-prompt the AI to fix that row.

Treat this like a real stakeholder document

In a real organization, the Authority Rules tab is what you present to the VP of Development or the database team before merging any records. If the reasoning column does not make sense to a non-technical reader, rewrite it. Clear, plain-language justification is what separates a Senior Director deliverable from an analyst's spreadsheet.

Your blueprint is complete. You have documented the mess, designed the target state, and defined the rules for resolving every conflict. Next up, you will put this blueprint to work and deduplicate the actual data.

Deduplicate the Data

Your blueprint defines exactly what a clean constituent record looks like and who wins when systems disagree. Now it is time to put those rules to work on real records.

In this step, you will merge all three messy CSV files into a single master list and document every merge decision in an audit trail. You will discover just how fragmented the Foundation's data really is by seeing the actual duplicate rate and the hidden giving that messy records were obscuring.

In this step, get ready to:
  • Run AI-powered deduplication to normalize, match, and merge records from all three source systems.
  • Execute the same deduplication logic in Power Query to create repeatable, documented transformation steps.
  • Compare both outputs to validate your clean master constituent list.
Run AI-powered deduplication on the source data

Deduplication is the process of identifying records that refer to the same person across different systems and merging them into a single "golden record." Your three source files have 265 total rows, but many of those rows are the same donors stored differently in each system.

  • Open Claude or ChatGPT and start a new conversation.
  • Upload all three CSV files (raiser_edge_export.csv, fundraise_up_export.csv, hubspot_export.csv) and your unified data model blueprint workbook to the conversation.
  • Paste Part A of the deduplication prompt into the chat.

What does Part A do?

Part A instructs the AI to normalize all name fields (stripping titles, suffixes, and inconsistent formatting), run email-based exact matching as the primary method, fall back to FirstName + LastName + ZipCode matching for records without matching emails, and apply your blueprint's survivorship rules to resolve conflicts.

The output is five files that together form your complete deduplication result and audit trail.

  • Wait for the AI to process the data and generate the five output files.
  • Download each file and save it to your Enterprise-Data-Unification project folder:
  • Open summary_stats.csv in Excel and confirm that the total source records value is 265.
  • Open master_constituent_list.csv and scroll through a few rows. Each row should have a unique MasterID.

AI not generating all five files?

If the AI only produces some of the files, ask it to generate the remaining ones explicitly by name. Long conversations with multiple file uploads can sometimes cause the AI to lose track of requested outputs.

If the files look empty or malformed, start a fresh conversation, re-upload all four files, and paste Part A of the prompt again.

Help me troubleshoot my AI deduplication output

Replicate the deduplication in Power Query

The AI output gives you clean data, but it does not show your work. Power Query creates the same result with documented, repeatable transformation steps that any team member can review, audit, and rerun.

Why Power Query instead of Python?

The Parkinson's Foundation runs on Microsoft 365. Power Query is built into both Excel and Power BI, so the transformations you build here carry directly into dashboard development.

A Senior Director builds processes that teams can maintain. Power Query transformations are visual, documented, and accessible to non-technical team members. A Python script only the author can run is a single point of failure.

  • In the same Claude or ChatGPT conversation, paste Part B of the deduplication prompt.
  • The AI generates Power Query M code for each deduplication phase (normalization, email matching, name+zip matching, survivorship) along with step-by-step instructions for executing them.
  • Open a new blank workbook in Excel.
  • Click the Data tab in the ribbon to access Power Query.
  • Follow the step-by-step execution instructions the AI provided. For each phase, open the Advanced Editor in Power Query, paste the M code for that phase, and run the query.
  • Repeat for each deduplication phase until all queries are loaded.

What is the Advanced Editor?

The Advanced Editor is where Power Query shows the raw M code behind each transformation step. Instead of building queries with the visual interface, you paste the AI-generated M code directly here. Each query you create becomes a documented, named tab in your workbook.

  • After all queries finish, review the output tables in the Power Query Editor. Each phase should show its results as a separate query.
  • Click Close & Load to send the results to Excel worksheets.

Seeing errors in the Power Query Editor?

M code is whitespace-sensitive. Make sure you copied the entire code block from the AI output, including the opening let and closing in statements.

If a query references a file path, update it to match the actual location of your CSV files in the Enterprise-Data-Unification folder on your computer.

Help me debug my Power Query M code errors

Validate the results against the AI output

You now have two versions of the same result. The AI-processed files are your answer key. The Power Query workbook is your portfolio evidence.

  • Open the Power Query output for the master constituent list and compare it to master_constituent_list.csv from the AI. The row counts should match and the MasterIDs should be identical.
  • Spot-check three records that were merged. Confirm the CRM name was kept, the most recent email was kept, and giving history was aggregated across sources.
  • If the outputs differ, use the AI files as the reference and debug the Power Query steps until they match.

Before you check the final summary, think about this: you started with 265 records across three systems. How many unique constituents do you think remain after deduplication?

  • Open summary_stats.csv and check the unique constituent count.

The total source records should read 265, and the unique constituents should be a noticeably smaller number. That gap is the fragmentation you just eliminated.

  • Open merge_log.csv and spot-check three merged records. For each one, verify:

Why does the merge log matter?

In enterprise data operations, every merge decision must be traceable. If a stakeholder asks why a donor's name changed or why two records were combined, the merge log provides the documented answer. This audit trail is what separates a professional data operation from a quick spreadsheet cleanup.

  • Save the completed Excel workbook with all Power Query tabs to your Enterprise-Data-Unification folder.

Your data is clean. Every donor has one record, every merge is documented, and you can prove every decision. But here is the reality: the next donation, the next email signup, and the next event registration are all potential duplicates. Without a system to catch them, your clean data starts degrading immediately.

In the next step, you will build that system with Power Automate so the cleanup sticks.

Automate Duplicate Detection with Power Automate

Your master constituent list is clean. Every record has a unique MasterID, authority rules are documented, and the merge log proves every decision. That is a strong foundation.

Here is the problem: every new donation, event registration, and email signup is a potential duplicate. Without automation, data quality degrades faster than any team can manually clean it. Power Automate turns your one-time cleanup into an ongoing system that catches duplicates the moment they arrive.

In this step, get ready to:
  • Create four SharePoint lists to house your master data and handle incoming records.
  • Build a Power Automate cloud flow that automatically detects duplicates in new records.
  • Test the flow with three scenarios to verify it catches duplicates and logs clean additions.
Create four SharePoint lists

Your flow needs somewhere to store data in the cloud. SharePoint lists act as lightweight databases inside your Microsoft 365 tenant. You will create four lists that mirror the data ecosystem your flow will manage.

  • Navigate to your SharePoint site in your browser.
  • Select Site contents from the left navigation, then select + New and choose List.

You will create the first list by importing your clean CSV. The other three lists start blank.

  • Select From CSV and upload your master_constituent_list.csv file.
  • Name this list Master Constituent List.
  • Review the column types that SharePoint infers from your CSV data. Adjust any columns that should be a specific type (for example, currency fields should be set to Currency, not plain text).
  • Select Create.

Your clean master data is now live in SharePoint. Confirm you can see the imported records by scrolling through the list.

  • Now create three blank lists. Go back to Site contents and select + New > List > Blank list for each.
  • Create the first blank list and name it Incoming Records. This list receives new records that need duplicate checking.
  • Add columns to Incoming Records that match the fields your flow needs to check: at minimum, columns for first name, last name, email, and zip code. Use + Add column in the list header to add each one.
  • Create the second blank list and name it Duplicate Review Queue. Add columns for MatchMethod (Single line of text) and ReviewStatus (Single line of text), plus columns to hold the incoming record details and the matched master record ID.
  • Create the third blank list and name it New Records Log. Add columns to track clean new additions (name, email, the newly assigned MasterID, and date added).

You now have four lists in your SharePoint site. Verify by going back to Site contents and confirming all four lists appear: Master Constituent List, Incoming Records, Duplicate Review Queue, and New Records Log.

Why four separate lists?

Each list serves a distinct role in the data pipeline. The Master Constituent List is the single source of truth. Incoming Records is the intake point. The Duplicate Review Queue holds flagged records for human review. The New Records Log provides an audit trail of clean additions.

Separating these concerns means your flow can operate on one list without accidentally modifying another. It also gives you clear audit trails for every decision.

Don't see all four lists in Site contents?

Make sure you selected Create after naming each list. If a list is missing, go to Site contents and create it again.

If the CSV import failed for the Master Constituent List, check that your master_constituent_list.csv file is a valid CSV with headers in the first row.

Help me troubleshoot my SharePoint list creation. I'm trying to create four lists for a data unification project and one or more are not appearing in Site contents.

Build the duplicate detection flow in Power Automate

Your lists are ready. Now you need a flow that watches the Incoming Records list and automatically checks every new entry against the master list for duplicates.

  • Navigate to make.powerautomate.com in your browser and sign in with your Microsoft 365 account.
  • Select My flows in the left navigation.
  • Select New flow, then select Automated cloud flow.
  • Name your flow Duplicate Detection - Incoming Records.
  • Under Choose your flow's trigger, search for and select When an item is created - SharePoint.
  • Select Create.

The flow designer opens with your trigger card ready to configure.

  • In the trigger card, set the Site Address to your SharePoint site URL.
  • Set the List Name to Incoming Records.

What does this trigger do?

The When an item is created trigger fires automatically every time someone adds a new row to the Incoming Records list. Your flow runs without anyone clicking a button.

Now build the flow logic step by step. The flow needs to normalize the incoming data, check for an email match, fall back to a name+zip match, and route the result to the correct list.

  • Add an action to normalize the incoming record's email to lowercase. Use a Compose action with a toLower() expression applied to the email field from the trigger.
  • Add a Get items action pointing at the Master Constituent List. Use a Filter Query to find records where the email matches the normalized incoming email.
  • Add a Condition action to check whether the Get items action returned any results. If the result count is greater than 0, an email match was found.

Configure what happens in each branch of the condition.

  • In the If yes branch (email match found): add a Create item action to write a row to the Duplicate Review Queue list. Set the MatchMethod field to Email Match and the ReviewStatus field to Pending. Include the incoming record details and matched master record ID.
  • Still in the If yes branch: add a Send an email (V2) action from the Office 365 Outlook connector. Set the To field to your own email address. Include the duplicate details in the body so you know which record was flagged.
  • In the If no branch (no email match): add a second Get items action to check the Master Constituent List for a name+zip match. Use a Filter Query that matches on first name, last name, and zip code.
  • Add another Condition inside the If no branch to check whether this name+zip query returned results.
  • If the name+zip match finds a result: add a Create item action to write to the Duplicate Review Queue with MatchMethod set to Name+Zip Match and ReviewStatus set to Pending. Add the same email notification action.
  • If no match is found at all: add a Create item action to add the record to the Master Constituent List as a new constituent. Add another Create item action to log the addition in the New Records Log.
  • Select Save on the command bar at the top of the designer.

What is the flow doing?

Your flow follows the same matching logic you used during deduplication. Email is the primary match key because it is unique. When no email match exists, the flow falls back to first name + last name + zip code as a secondary match.

Matches go to a human review queue rather than being automatically merged. This protects against false positives. Records with no match at all are added directly to the master list and logged for audit.

Use dynamic content for field mapping

When configuring each action, Power Automate shows a Dynamic content panel with fields from previous steps. Use these to map incoming record fields into your list actions. This ensures the flow passes the actual data from the trigger, not static values.

Flow won't save?

Every cloud flow needs at least one trigger and one action. Make sure each branch of every Condition has at least one action inside it.

If you see a connection error, select the action with the warning and re-authenticate your SharePoint or Outlook connection.

Help me troubleshoot my Power Automate cloud flow. It won't save and I'm building a duplicate detection flow with SharePoint triggers and conditions.

Test the flow with three scenarios

A flow that looks correct in the designer might still fail with real data. You need to test all three paths: email match, name+zip match, and completely new person.

Before you run each test, think about what you expect to happen. Which list should receive a new row? What values should appear in the MatchMethod and ReviewStatus columns?

  • Open the Incoming Records list in SharePoint.
  • Select + Add new item.
  • Enter a test record with an email address that matches someone already in the Master Constituent List. Fill in the name and zip fields as well. Select Save.

This triggers your flow. Give it a minute to run.

  • Return to make.powerautomate.com.
  • Select My flows and select your Duplicate Detection - Incoming Records flow.
  • Check the Run history section. You should see a new run with a status of Succeeded. Select the run to expand the details and confirm each step shows a green checkmark.
  • Open the Duplicate Review Queue list in SharePoint. You should see a new row with MatchMethod set to Email Match and ReviewStatus set to Pending.
  • Check your email inbox for the duplicate notification.

What should I see?

The Duplicate Review Queue has exactly one new row from this test. The MatchMethod column reads Email Match because the incoming email matched a record in the master list. The ReviewStatus column reads Pending because the duplicate has not been resolved yet.

Now test the second scenario.

  • Go back to the Incoming Records list and add a new item. This time, use a different email address that does NOT exist in the master list, but enter a first name, last name, and zip code that match an existing master record.
  • Wait for the flow to run, then check the Run history to confirm it succeeded.
  • Open the Duplicate Review Queue. You should see a second row, this time with MatchMethod set to Name+Zip Match.

Now test the third scenario.

  • Add one more item to the Incoming Records list. This time, use a completely new person with a unique email, name, and zip code that do not match anyone in the master list.
  • Wait for the flow to run and verify it succeeded in Run history.
  • Open the Master Constituent List. Scroll to the bottom. You should see the new person added as a new row.
  • Open the New Records Log. You should see a new entry tracking this addition.

Why three separate tests?

Each test validates a different branch of your flow's logic. The email match test proves the primary duplicate detection works. The name+zip test proves the fallback logic works. The new person test proves that clean records flow into the master list correctly.

If any test fails, open the failed run in Run history and expand the step with the red exclamation icon to see the error details.

Flow run shows as Failed?

Open the failed run in the Run history section and expand the step marked with a red exclamation icon. The error message tells you exactly what went wrong.

Common causes: the Filter Query syntax in the Get items action has a typo, the column names in your Create item actions don't match the actual SharePoint list column names, or a required field was left empty.

Help me debug my Power Automate flow run failure. I'm testing a duplicate detection flow with SharePoint lists and one of the three test scenarios failed.

Your duplicate detection system is live. Every new record that enters the Incoming Records list is automatically checked against the master list, flagged if it matches, and logged if it is new. Next up, you will build the dashboard that proves the impact of everything you have built to leadership.

Build the Dashboard and Host Your Portfolio

In the previous step, you built a Power Automate cloud flow that protects your clean data from future duplicates. Your data model is documented, your master list is clean, and your automation is live.

None of that matters if leadership cannot see the impact. Clean data and automated processes are invisible to a VP unless you translate them into donor counts, giving totals, and data quality trends.

In this step, you will build a four-page interactive dashboard populated with your real project data and host it as a live portfolio link.

In this step, get ready to:
  • Generate a four-page HTML dashboard populated with your real project data.
  • Host the dashboard on GitHub Pages as a shareable portfolio link.
  • Review the dashboard end to end as a leadership presentation.
Generate the dashboard with AI

The dashboard needs to reflect your actual deduplication results, not placeholder data. You will upload your real output files to AI and have it generate a single HTML file that uses Chart.js for interactive charts.

Before you can generate the dashboard, you need to export the test data from your two SharePoint lists that captured activity during your flow testing.

  • Return to your SharePoint site and open the Duplicate Review Queue list.
  • Make sure no items are selected in the list.
  • In the command bar at the top, click Export, then select Export to CSV.
  • Save the downloaded CSV file to your Enterprise-Data-Unification folder.
  • Repeat the same export for the New Records Log list and save that CSV to the same folder.

Why export from SharePoint?

The Duplicate Review Queue and New Records Log captured real activity from the three test scenarios you ran in the previous step. Including this data in the dashboard proves the automation system is operational, not just theoretical.

You now have all the data files the dashboard needs.

  • Switch back to Claude or ChatGPT.
  • Upload all of the following files from your Enterprise-Data-Unification folder:
  • master_constituent_list.csv
  • merge_log.csv
  • match_results.csv
  • summary_stats.csv
  • The exported Duplicate Review Queue CSV.
  • The exported New Records Log CSV.
  • Use the HTML dashboard prompt from your project materials to generate a single HTML file with all four dashboard pages populated from your real data.

What are the four dashboard pages?

Page 1 (Executive Summary) shows the problem and the fix: total source records, unique constituents after dedup, duplicate rate, and merges performed.

Page 2 (Data Quality Scorecard) proves data quality is measured: overall completeness score, field-by-field completeness, and completeness by source system.

Page 3 (Fundraising Impact) connects clean data to fundraising outcomes: total giving before and after, donors reclassified to higher tiers, and true retention rate.

Page 4 (Operational Health) demonstrates the system is ongoing: records processed, duplicate catch rate, average resolution time, and queue backlog.

  • Copy the full HTML output from the AI response.
  • Open a text editor (Notepad on Windows, TextEdit in plain text mode on macOS, or your preferred editor).
  • Paste the HTML content and save the file as index.html in your Enterprise-Data-Unification folder.

Saving with the correct extension on Windows

In Notepad, set the "Save as type" dropdown to "All Files" before saving. This prevents the editor from appending .txt to the filename.

Before you open the file, take a moment to predict. Based on the data you cleaned in Step 3, do you expect the duplicate rate to be above or below 30%?

  • Open index.html in your browser by double-clicking the file.

You should see the Executive Summary page load with KPI cards showing your actual data. The total source records should show 265, and the unique constituents number should be lower, reflecting the duplicates you eliminated.

  • Click through all four pages to confirm each one loads with charts and KPIs populated from your real data.

Dashboard shows blank charts or no data?

  • Make sure you uploaded all six data files to the AI before generating the HTML. Missing files mean missing data on specific pages.
  • Check that you saved the file with the .html extension, not .txt. If your browser shows raw HTML code instead of a dashboard, the file extension is wrong.
  • If a specific page is empty, re-upload the missing file and ask the AI to regenerate just that page's data section.

Help me debug my HTML dashboard

Host the dashboard on GitHub Pages

Your dashboard works locally, but a portfolio piece needs a shareable URL. GitHub Pages hosts static HTML files directly from a repository for free.

  • Open your browser and navigate to github.com.
  • Click the + icon in the top right corner.
  • Select New repository.
  • Enter data-unification-dashboard as the repository name.
  • Set the visibility to Public.
  • Click Create repository.

Why does the repository need to be public?

GitHub Pages is free for public repositories. A public repo means anyone with the link can view your live dashboard, which is exactly what you want for a portfolio piece.

Your empty repository is ready for the dashboard file.

  • On the repository's Quick Setup page, click uploading an existing file.
  • Drag your index.html file from your Enterprise-Data-Unification folder into the upload area.
  • Click Commit changes.

You should see index.html listed in your repository's file list.

The file is in the repository, but it is not yet served as a live website. You need to enable GitHub Pages to make it publicly accessible.

  • Click the Settings tab at the top of your repository.
  • In the left sidebar, under the Code and automation section, click Pages.
  • Under Build and deployment, find the Source dropdown and select Deploy from a branch.
  • In the branch dropdown, select main and leave the folder set to / (root).
  • Click Save.

How long does deployment take?

GitHub Pages typically deploys within one to two minutes. You can check the status by clicking the Actions tab in your repository. When the workflow run shows a green checkmark, your site is live.

  • Wait about one minute for the deployment to complete.
  • Click the Settings tab again, then click Pages in the left sidebar.

You should see a message at the top of the page with your live URL. Your dashboard URL follows this pattern:

https://[[GITHUB_USERNAME="your GitHub username"]].github.io/data-unification-dashboard
  • Click the URL displayed on the Pages settings page to open your live dashboard.

You should see the same four-page dashboard you tested locally, now hosted at a public URL.

Seeing a 404 page?

  • Make sure the file in your repository is named exactly index.html (all lowercase). GitHub Pages looks for this specific filename.
  • Confirm you selected main as the branch and / (root) as the folder in Pages settings.
  • Check the Actions tab for a failed deployment. If the workflow failed, click on it to see the error details.

Help me troubleshoot my GitHub Pages 404 error

Review the dashboard as a portfolio presentation

The final check is to verify the dashboard works as a portfolio piece. You will walk through each page the way a Senior Director would present it to a VP.

Before you open the link in a different browser, predict this: will the dashboard load identically for someone who has never seen your GitHub account?

  • Copy your GitHub Pages URL from the browser's address bar.
  • Open a different browser (or an incognito/private window) and paste the URL.

The dashboard should load identically. This confirms the link is publicly accessible to anyone, including an interviewer.

  • On Page 1 (Executive Summary), confirm the KPIs show the problem and the fix: total source records (265), unique constituents after dedup, duplicate rate, and records eliminated.
  • Navigate to Page 2 (Data Quality Scorecard) and confirm the field completeness chart shows data quality is measured, not assumed.
  • Navigate to Page 3 (Fundraising Impact) and confirm the giving tier chart shows donors reclassified to higher levels after deduplication.
  • Navigate to Page 4 (Operational Health) and confirm the automation metrics reflect your Power Automate flow activity.

How would you present each page?

Page 1 tells the story: here is the problem (fragmented data across three systems) and here is the fix (a unified master list with documented merge decisions).

Page 2 proves rigor: data quality is tracked with real metrics, not gut feeling.

Page 3 connects the work to revenue: clean data revealed hidden giving and reclassified donors. Page 4 proves sustainability: the system is ongoing, not a one-time cleanup.

Your data operations portfolio piece is live. You have a unified data model, clean data, automated protection, and a dashboard that proves the impact to leadership.

Secret mission

Rebuild the Dashboard in Power BI Desktop

Your HTML dashboard is live and shareable as a portfolio link. But the job description specifically lists Power BI as a preferred skill. Rebuild the same four-page dashboard in Power BI Desktop using DAX measures and formatted visuals to demonstrate hands-on proficiency with the exact tool the role requires.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely within your existing Microsoft 365 subscription and free services, so there are no ongoing costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • Four SharePoint lists (Master Constituent List, Incoming Records, Duplicate Review Queue, New Records Log) in your Microsoft 365 tenant.
  • One Power Automate cloud flow for duplicate detection at make.powerautomate.com.
  • One GitHub repository (data-unification-dashboard) with a GitHub Pages site hosting your HTML dashboard.
  • Local project files in the Enterprise-Data-Unification folder (CSV datasets, Excel workbooks, Power BI .pbix file).

Keep everything running

No action needed. Choose this if you are still actively building or want to keep your portfolio dashboard live.

  • Your four SharePoint lists persist in your Microsoft 365 tenant at no additional cost beyond your existing subscription.
  • The Power Automate cloud flow remains active and continues detecting duplicates when new records arrive in the Incoming Records list.
  • Your GitHub Pages dashboard stays live at your public URL for anyone to view.
  • Local files remain on your computer in the Enterprise-Data-Unification folder.

Pause - I'll come back to this later

Disable the automation to stop it from processing new records while keeping everything in place for later.

  • Navigate to make.powerautomate.com and sign in with your Microsoft 365 account.
  • Find your duplicate detection flow under My flows.
  • Turn off the flow to stop it from triggering on new records.
  • Your SharePoint lists, GitHub Pages site, and local files all remain accessible when you are ready to return.

Delete - I don't want to use this again

Remove all project resources and start fresh. Follow each group of instructions below.

SharePoint lists:

  • Navigate to your SharePoint site and open Site contents.
  • Delete each of the four lists: Master Constituent List, Incoming Records, Duplicate Review Queue, and New Records Log.

Power Automate flow:

  • Navigate to make.powerautomate.com and find your duplicate detection flow under My flows.
  • Select the flow and delete it.

GitHub repository:

  • Navigate to your data-unification-dashboard repository on GitHub.
  • Click Settings in the repository navigation bar.
  • Scroll to the bottom of the page and delete the repository. This also removes your GitHub Pages site.

Local project files:

  • Delete the Enterprise-Data-Unification folder from your computer. This removes all CSV datasets, Excel workbooks, and the Power BI .pbix file.

Nice Work!

Nice Work!

Nice work! You just built a complete enterprise data unification system for a national nonprofit.

Starting with messy, fragmented donor records from three platforms, you designed the data model, cleaned the records, automated ongoing protection, and proved the results with a live dashboard that any Senior Director could present to leadership.

You've learned how to:

  • Design a unified constituent data model that maps donor data from three nonprofit systems (Raiser's Edge, Fundraise Up, HubSpot) into a single golden record structure, then deduplicate 265 messy source records into a clean master list using Power Query with a full merge audit trail documenting every decision.
  • Build a Power Automate cloud flow that automatically detects duplicates in every new incoming record, routes matches to a SharePoint review queue for human decision, and logs clean additions to the master list.
  • Create a four-page interactive dashboard proving data quality and fundraising impact to leadership, and host it as a live portfolio link on GitHub Pages.
  • Secret Mission: Rebuild the dashboard in Power BI Desktop with DAX measures and formatted visuals, demonstrating hands-on proficiency with the exact tool the role requires.

Ready to quiz yourself?