Power QueryExercises

CRM deduplication

Brightwater Events (fictional)

Bring web sign-ups and event badge scans together, respect deletion requests, and build one golden record per person.

Level
advanced
Time
~65 min
Deliverables
2
Rubric
v1

Access

Pro. This brief is public. The data pack, workspace, hints, grading and solution explanations need Pro, Lifetime Learning or a seat in an organization. See plans.

Each deliverable's solution explanation unlocks when it passes. You can also unlock it early once you have used every hint for that deliverable (this needs an account so the help is recorded, and the deliverable then counts as completed with help).

Business goal

Give the marketing team one clean contact per person, with where they came from, so the next mailing reaches each person once and never reaches anyone who asked to be forgotten.

Brightwater runs trade events. People sign up on the website, and at the door their badge is scanned by an app that exports JSON. The same person often appears several times across both systems, typed differently each time. Some people have asked for their data to be deleted; their addresses are on a suppression list that must always win.

Graded deliverables

  1. 1. Contact Records

    50 points

    Every usable record from both systems in one shape, normalised and with deletion requests removed.

    • Columns: Source, SourceID, Name, Email, Phone, SeenOn
    • Email lower case and trimmed; Phone digits only or null
    • No rows without a usable email; no suppressed address
    Expected output schema for Contact Records
    ColumnType
    Sourcetext
    SourceIDtext
    Nametext
    Emailtext
    Phonetext or null
    SeenOndate
    Grain:
    One row per web sign-up or badge scan that has a usable, non-suppressed email.
    Order:
    Not graded.
    Nulls:
    Phone is null when the record has no digits; nothing else is null.
  2. 2. Golden Contacts

    50 points

    One row per email address, built from that person's records.

    Builds on: Contact Records

    • Columns: Email, Name, Phone, FirstSeen, LastSeen, Sources, Records
    • Exactly one row per email
    • Name from the latest record; Phone from the latest record that has a phone
    • Sorted by Email
    Expected output schema for Golden Contacts
    ColumnType
    Emailtext
    Nametext
    Phonetext or null
    FirstSeendate
    LastSeendate
    Sourcestext
    Recordswhole number
    Grain:
    One row per normalised email address.
    Order:
    Graded: Email ascending.
    Nulls:
    Phone is null only when none of the person's records has a phone.
Checking your access…