Kemang Data

Power BI · Data cleaning

Event Visitor Registration Dashboard

An event organizer sent visitor registrations as 3 messy files. We merged and cleaned them, then built one dashboard to track registrations, attendance and channels.

  1. 1
    Combine3 files, 6,060 rows
  2. 2
    Cleanduplicates, phones, emails, names
  3. 3
    MeasureDAX for the 3 KPIs
  4. 4
    Dashboard6,000 clean rows, 3 views →
6,000registrations
57.53%attendance
47.15%Jabodetabek
Dashboard overview with all 6,000 registrations
Overview. All 6,000 registrations by day, hour, business, job and channel.
Dashboard filtered to Jabodetabek, showing 2,829 registrations
Region filter. Jabodetabek selected: every chart recalculates to 2,829 registrations.
Dashboard with the Restaurant & Cafe bar selected, highlighting its share in every chart
Cross-filter. Click Restaurant & Cafe and its 1,236 registrations highlight in every chart.

What we did

What we did

  • Combined 3 source tables in Power Query and removed duplicate ticket codes.
  • Standardized names, cities, emails and phone numbers (all to the 62… format).
  • Filled 120 blank job titles as "Unknown" so no visitor drops out of reports.
  • Added calculated columns: Region Group, Job Segment, Registration Date and Hour.
  • Wrote DAX measures for registrations, attendance and regional share.

What the dashboard answers

  • When people register: by day and by hour.
  • Who registers: business nature and job segment.
  • Which channels bring people who actually show up.
  • Filters for attendance status and region.

Step 2 · Before and after cleaning

Real rows from the source files and how they look after the Power Query steps.

FieldBeforeAfter
Full nameKIKI HIDAYATKiki Hidayat
Full namecitra permataCitra Permata
CityBANDUNGBandung
Phone0803-2848-43306280328484330
Phone+62 880 1551 127628801551127
Phone08260440952628260440952
Emaileka.halim63@gmial.comeka.halim63@gmail.com
Emailgita.nugroho22@example.co,idgita.nugroho22@example.co.id
Job title(blank)Unknown

Synthetic data for portfolio demonstration. Duplicates were removed by Ticket Code, keeping the first record found.

Step 3 · Selected DAX

The measures behind the three headline cards.

Total Registrations = COUNTROWS ( Registrations )

Attended =
CALCULATE ( [Total Registrations], Registrations[Status] = "Attended" )

Attendance Rate = DIVIDE ( [Attended], [Total Registrations] )

Jabodetabek Share =
DIVIDE (
    CALCULATE ( [Total Registrations], Registrations[Region Group] = "Jabodetabek" ),
    [Total Registrations]
)

Need a dashboard or cleaner data?

Send us a sample of your data and what you want to track. We will tell you what we can build and how long it takes.

Chat me on WhatsAppEmail usUpwork profileGitHubLinkedIn