Learning Generative AI Tools for Excel

Chapter 6. Data Preparation with Copilot

Many years ago, I found myself tackling a predictive modeling project for the very first time. The goal was to forecast ship routes as part of a project funded by the European Space Agency (ESA). It was also my first real encounter with massive datasets concerning historical ship route data. The dataset comprised approximately 80 million records, provided by two different providers. At the time, that number felt enormous to me. This was also my first hands-on experience building a real-world machine learning model.

I still vividly remember the first version of the model I built. I trained the model using approximately 20 million records and then tested it on the remaining 60 million records. The result was a complete disaster.

First off, the training process took over a month. But that wasn’t the worst part; the model’s predictions were totally off. It just couldn’t get the ship routes right. But I didn’t give up. I decided to take a deep dive into the data, examining it in fine detail to gain a deeper understanding of what was really going on. That’s when everything started to make sense. I realized that the two data providers had given me almost identical data, so nearly every record was duplicated. In other words, I didn’t actually have 80 million records. I had more like 40 million. Then I noticed other issues: incorrectly formatted numbers, missing values, fields that had been interpreted as text instead of numeric values…the list ...