Description
Master this data cleaning challenge using real World Cup soccer statistics. Learn how to fix messy, multi-line goal entries to a neat table (or data frame).
This tutorial walks you through a practical data cleaning challenge where I address a common issue: multiple goals recorded in a single cell separated by new line characters. This is essential knowledge for anyone looking to clean data efficiently.
I am showing both Python & Power Query approaches for cleaning the data. Which one is your favourite? Comment and let me know.
📁 Try it yourself:
Grab the raw data here and try it yourself -
https://chandoo.org/wp/wp-content/uploads/2026/06/fifa-worldcup-goals-cleanup.xlsx
👩💻🧑💻 Completed code:
See my github repo for the python notebook and PQ workbook.
Python notebook: https://github.com/chandoo-org/python/blob/ba747ec6c5373c34ee35826d6a1c6822c42bae0f/fifa%20goals%20data%20cleanup.ipynb
Power Query File: https://chandoo.org/wp/wp-content/uploads/2026/06/fifa-goals-pq.xlsx
🕐 Video Topics:
0:00 - Introduction to the data cleaning challenge
1:32 - Data Cleaning with Python (loading data to Pandas Dataframe)
2:30 - Splitting and combining all data to one DF
4:15 - Splitting goals by new line and exploding the Data Frame
5:08 - Dealing with multiple goals by same person
8:55 - Removing un-needed columns and tagging each goal by type (penalty, own goal etc.)
10:36 - Possible next steps for this data...
11:13 - Loading data into Power Query
12:00 - Splitting and appending tables in PQ
13:20 - Separating each line to new record
14:25 - Separating scorer and goal time details to two columns
18:33 - Splitting multiple goals by same person to different rows
19:02 - Tagging goals by their type (penalty, own goal etc.)
19:50 - Loading data into Excel
20:13 - Closing & bloops
~
#Powerquery #python #fifa