Clean data before calculating — Smart Study Module 1

A monthly collection report is only useful if every payment is counted once. All supplied names and amounts are fictional.

Outcomes

• Identify the grain and primary key
• Import dates and amounts with correct types
• Reconcile row counts and control totals

Understand

payments.csv has one row per payment_id; loans.csv has one row per loan_id at 31 January 2026. Identifiers are text, amounts are numeric rupees and dates use ISO YYYY-MM-DD. POS is a snapshot, not a payment amount.

Import using Data → From Text/CSV instead of relying on double-click detection. Set payment_id and loan_id to text, payment_date to Date and amount to Whole Number. Convert each imported range to an Excel Table and use descriptive table names.

Keep an untouched raw copy. A blank amount is missing evidence, not automatically zero; quarantine it and investigate. Sorting one column independently breaks row relationships. Format changes do not convert numeric text into numbers.

Follow along

1. Download the shared project kit and read README before opening data.
2. Import all nine payments. Select the whole table before sorting.
3. Filter January using dates at or after 2026-01-01 and before 2026-02-01.
4. Check payment_id uniqueness and compare totals with the control sheet.

Worked example

=SUM(Payments[amount])
=COUNT(Payments[amount])

Nine payments total ₹98,000 across both months; five January payments total ₹48,000.

Practice

Import payments and return the all-period amount total as a number.

Create a free personalized study plan · Download project instructions

Interactive practice runs in your browser.