Ready!
Ghosts in the Data!
The Accounts Payable database is contaminated with raw, unstructured inputs. We suspect duplicate invoices and unauthorized maverick spending. Your mission: Build a localized ETL pipeline to eat the dots, clean the logs, and extract the fraudulent records.
Level Objectives
Transform
- > Trim_Strings()
- > Clean_DataTypes()
- > Format_Syntax()
Extract/Load
- > Import_CSV()
- > Outer_Join()
- > Expand_Data()
Controls
- > Detect_Dupes()
- > 3-Way_Match()
- > Flag_Orphans()
Study The Map
Review the data tables below to understand the maze architecture before extracting them to your local drive.
Raw_AP_Transactions.csv
The raw operational output from the legacy accounting mainframe.
IDENTIFIED GHOSTS (ERRORS)
Inconsistent vendor casing ("Staples" vs "STAPLES"), corrupted amount strings containing symbols ($), and unstandardized department codes. Requires immediate sanitation.
Vendor_Alias_Map.csv
The Rosetta Stone provided by the finance division.
INTEGRATION PLAN
Because AP clerks input "UPS 0039" and "U.P.S." randomly, standard grouping fails. This mapping table must be joined to translate raw text into unified official names.
Vendor_Master.csv
The secure Master Database.
REQUIREMENT
Contains golden `VendorID` data. You cannot successfully join this table until Step 2 is fully executed and aliases are resolved.
Purchase_Orders.csv
The approved purchasing ledger used for fraud audits.
THE TRAP
Used to perform a 3-way match. Any discrepancy between the invoice amount and this PO amount indicates potential embezzlement or policy violation.
Level 1
Clear the maze data.
Insert Coin (Download)
- Create a new folder on your computer named
PAC-ETL-Mission. - Download the 4 raw CSV files using the button above.
- Move all 4 downloaded CSV files into your newly created folder.
Player 1 Start (Power BI)
- Open Power BI Desktop and close any welcome pop-ups to view a blank report.
- > This Power BI file will act as your ETL engine to process and store the final output from your raw CSVs.
Eat Raw Data
- In Power BI, navigate to the Home tab → Get Data → Text/CSV.
- Target Raw_AP_Transactions.csv → Execute Transform Data.
- Rename query parameter to:
AP_Raw
Chomp The Noise
- Vendor: Execute Transform → Format → Trim. Then Clean.
- Amount: Replace Values
$→ (blank). Replace,→ (blank). - Amount: Mutate Data Type to Decimal Number.
> ANSWER: Computers are literal. A leading space makes it an entirely different string. " STAPLES " will not match with "STAPLES" in our alias map. Trimming invisible whitespace is the #1 fix for failed joins.
Temporal Sync
InvoiceDate: Click Type Icon (ABC) → Using Locale → Date (English US).
Bypasses localization errors and unifies mixed temporal arrays like 1/15/2025 and 2025-01-16.
> ANSWER: If dates remain formatted as "Text", you cannot sort them chronologically (e.g., February 2024 would sort alphabetically before January 2024), nor can you extract Month/Year for period-over-period financial reporting.
Standardize Paths
- Finance → FIN
- Fin → FIN
- Operations/Ops → OPS
- Warehouse → WH
- NET30/Net30 → Net 30
- DOR → Due on receipt
- NET15 → Net 15
Map The Tunnels (Merge)
- Import New Source →
Vendor_Alias_Map.csv. - Execute Use First Row as Headers. Clean
VendorRawcolumn. - Return to
AP_Raw. Execute Merge Queries (Left Outer) joining on Vendor. - Expand payload to extract only
VendorName_Clean.
> ANSWER: An Inner Join drops any row that doesn't have a match. We can't just "delete" an invoice because a vendor was missing from our map - that understates liabilities! A Left Outer Join keeps all AP invoices and puts a "null" where mapping is missing, flagging it for review.
Master DB Uplink
- Import New Source →
Vendor_Master.csv. Clean VendorName. - Merge into
AP_Rawlinking viaVendorName_Clean(Left Outer). - Expand payload to extract:
VendorID,Category.
Fraud Trap Alg.
- Import
Purchase_Orders.csvand merge into AP_Raw viaPO_Number. ExpandPO_Amount. - Deploy Conditional Column
POMatchStatus:
ELSE IF Amount equals PO_Amount THEN "Match"
ELSE "Mismatch"
> ANSWER: It means the business spent money without getting pre-approval from procurement. This is a significant red flag for unauthorized spending, maverick purchasing, or potential fraud. It requires immediate manager review.
Duplicate Sweep
- Right-click
AP_Raw→ Duplicate query. Rename toDupCounts. - Isolate
VendorName_CleanandAmount(Remove others). - Execute Group By. Operation: Count Rows.
- Merge
DupCountsback intoAP_Raw. Expand Count. - Deploy Conditional Column
DuplicateFlag: If Count > 1 then TRUE else FALSE.
> ANSWER: Yes, recurring flat-fee services (like a $50/mo bill) will have identical amounts. However, seeing the exact same dollar amount billed by the exact same vendor on the same day is the mathematical fingerprint of a duplicate submission. It's a risk indicator, not a guarantee.
Final Extraction
- Right-click AP_Raw → Reference. Rename to
Exception_Report. - Filter to keep anomalies (Mismatches, Null Vendors, Duplicates).
- Rename AP_Raw to
Clean_AP_Transactions. - Execute: Close & Apply (from the Home ribbon) to load into the model.
- File → Save As: Name it
PQ_ETL_Lab.pbixand save it in your mission folder.
Level Cleared!
Data successfully decrypted, scrubbed, and secured.
> SUBMIT_SCORE_TO_CANVAS
- Save your completed
PQ_ETL_Lab.pbixfile (which now holds your loaded data). - Access the university Canvas portal.
- Upload the
PQ_ETL_Lab.pbixfile to the target drop zone to collect XP.
Are you finished? Play this game while you wait.
Click canvas or swipe to start. Use ARROW KEYS or SWIPE to move. Eat all dots to win!
TO START
Admin Override
Master Decryption Sequence (No Missed Steps)
- 1. Import: Data → Get Data → CSV →
Raw_AP_Transactions.csv→ Transform. RenameAP_Raw. - 2. Vendor: Transform → Format → Trim, then Clean.
- 3. Amount: Replace Values
$→ (blank). Replace,→ (blank). Data Type → Decimal Number. - 4. Date: Data Type → Date (Use Locale if needed).
- 5. Dept/Terms: Replace Values.
- 6. Alias Map: New Source CSV →
Vendor_Alias_Map.csv. Use First Row Headers. Clean VendorRaw. - 7. Merge 1: AP_Raw (Vendor) ↔ Alias (VendorRaw). Expand
VendorName_Clean. - 8. Master: New Source CSV →
Vendor_Master.csv. Clean VendorName. - 9. Merge 2: AP_Raw (Clean Name) ↔ Master (Clean Name). Expand ID, Category.
- 10. POs: New Source CSV →
Purchase_Orders.csv. Merge on PO_Number. Expand Amount. - 11. Logic: Add Conditional Col
POMatchStatus(Match/Mismatch/No PO). - 12. Duplicates: Right-click AP_Raw → Duplicate. Group By (Vendor+Amount). Merge back.
- 13. Exception Report: Reference AP_Raw. Filter for Mismatches/Nulls.
- 14. Final Load: Execute Close & Apply, then Save As
PQ_ETL_Lab.pbix.