Module 2 · Data Analyst Capstone: End-to-End BI Project
Profile the raw data
Check every column of a raw till export systematically, find the problems before they find you, and decide what to do about each one.
About 25 minutes
The problem
Before you calculate a single total, imagine the board meeting. A director asks, "How do you know these numbers are right?" If your answer is "I loaded the file and summed it", you're in trouble, because Voltline's till export has problems that would quietly inflate, split or distort almost every number in the report.
Profiling means looking at every column of every file systematically, before analysing anything, so you know exactly what you're dealing with. It's the step most beginners skip, and the step experienced analysts never do.
The concept
A profiling checklist
For each file, and each column in it:
| Check | Question | Finds |
|---|---|---|
| Row count | How many rows? Does that make sense? | missing or extra data |
| Key | Is the ID unique? Is the combination that should be unique, unique? | duplicates |
| Text values | What are the distinct values, and how many of each? | inconsistent spellings, stray spaces |
| Dates | What format? What's the earliest and latest? | mixed formats, impossible dates |
| Numbers | Min, max, any negatives or zeros? | returns, errors, outliers |
| Blanks | How many missing values per column? | gaps to explain |
| Relationships | Does every code match a row in its lookup file? | orphans, test data |
The same checks in each tool
| Check | Excel / Power BI | SQL | pandas |
|---|---|---|---|
| Distinct values and counts | Filter drop-down, or Power Query's Column distribution | SELECT col, COUNT(*) … GROUP BY col | df['col'].value_counts() |
| Min and max | MIN, MAX, or Column profile | MIN(col), MAX(col) | df.describe() |
| Exact duplicate rows | Remove Duplicates (count the difference) | COUNT(*) against COUNT(*) of SELECT DISTINCT * | df.duplicated().sum() |
| Codes with no match | XLOOKUP returning #N/A | LEFT JOIN … WHERE … IS NULL | merge(…, indicator=True) |
In Power Query, turn on View → Column quality, Column distribution and Column profile, and set profiling to the entire data set (bottom-left of the window). By default it only profiles the first 1,000 rows, and most of Voltline's problems are further down.
Every problem gets a decision
For each problem, record what you found, how many rows it affects and what you decided. Some problems you fix. Some you exclude. Some aren't problems at all (returns are real business events, not errors). That record, the data quality log, is what lets you answer the director's question.
Example
Profiling the branch column in SQL:
SQL
SELECT branch, COUNT(*) AS lines
FROM sales_raw
GROUP BY branch
ORDER BY branch;There are 10 distinct values for 8 stores. IKEJA and Ikeja Store are the same shop: the till's settings changed in September 2025. Port Harcourt and P/Harcourt are the same too, and Yaba has a trailing space that you can't even see in Excel.
But look at txn_id: every ID starts with a three-letter store code (IKJ-000123), and those codes match stores.csv exactly. The branch text is unreliable; the code inside the ID isn't. Finding a reliable column to replace an unreliable one is a typical profiling win.
Walkthrough
- Profile
branch, as in the example, and list the spellings for each store. - Profile
txn_date. Most dates look like2025-03-14, but one store's look like14/03/2025. Which store? In Excel these may turn into real dates or stay as text depending on your settings, so check carefully. - Look for exact duplicate rows. In pandas,
df.duplicated().sum(); in SQL, compareCOUNT(*)withSELECT COUNT(*) FROM (SELECT DISTINCT * FROM sales_raw). Then find which store and month they come from. - Profile
product_codeagainstproducts.csv. One code isn't in the product list. Look at those rows: their dates, prices and store. - Profile
qty. Some values are negative. Look at a few: are they errors, or returns? - Check
stores.csvagainst the sales: when did Lekki open, and do any sales appear before that? - Write each finding in your data quality log (the task below).
Practice
Practice
How many rows in sales_raw are exact duplicates of another row (the extra copies only)?
Practice
How many rows have a product_code that isn't in products.csv?
Practice
How many rows have their date in DD/MM/YYYY format?
Task
10 minWrite your data quality log: at least four problems you found in the raw data. Put each on its own line starting with -, in the form Issue | Rows affected | Decision, for example - Trailing spaces in branch | 3,559 | trim, then map to store codes.
Your work is checked for
- At least four entries, each a line starting with -
- Each entry has three parts separated by |
- Each entry gives a number of rows
- Covers the duplicate upload
- Covers the date format
- Covers the test transactions
Check your understanding
Answer every question to check.