Automated Transformations and Formulas
1. Automated Transformations and Formulas
Welcome back!2. Order! Order in the Court!
Raw data is often "messy". In real-world datasets, you’ll commonly find cells that contain multiple pieces of information—like the session info column shown here. You may also see inconsistent values across rows and columns. And in many cases, you’ll need to3. Order! Order in the Court!
transform the data to extract what you actually need—for example, separating first and last names from email addresses.4. The Traditional Workflow
Instead of spending time trying to track down the right functions, writing a formula - and scratching our heads when it inevitably doesn't work -5. The Copilot Workflow
Copilot can turn that messy data into clean columns without the headache of manually writing formulas. Notice there's still a validation step here, but instead of writing and debugging formulas by-hand, it's a conversation with Copilot focused on ensuring the inputs and outputs are correct. We'll see this workflow evolve throughout the course. Let's see this in action!6. Example: Splitting Text Cells
In this lesson, we'll clean text data through common transformations like splitting text and extracting based on patterns. However, this same process applies to any data type - it's the workflow that matters most. In our table of university session information, we'll begin by extracting the lecturer's name from their email. These have a standardized form: first name-dot-last name, then the university email domain. Because we're wanting Copilot to edit our worksheet to add the new columns, we'll use Agent Mode. Here, we'll ask Copilot to create two columns for first name and last name by separating the emails in the email column. Copilot thinks, then applies the formula directly to the worksheet to create the new columns. Our job is to review the results to check everything worked as expected. If it didn't we'd feed our observations on what went wrong back to Copilot, which would then act to fix its mistake.7. Example: Extracting Text Elements
Let's step this up a gear and look at how to extract elements from patterned text. The session info column has values like "Biology", "Monday", and "Session title", with brackets around some of the values. These can be extracted, but there's several text transformations to apply, which traditionally means a few google searches and documentation checks. Instead, we'll ask Copilot to extract the text from the second set of brackets into a new column. Agent Mode thinks, applies the formulas to extract the information, and adds the columns directly to the worksheet. We then review the result to make sure the extraction worked.8. A Prompting Checklist
Here's a few things to watch out for. First, make sure you're clear about which tables and columns Copilot should act on. Without this information, Copilot may take an educated guess, and that might not always be right. Second, if your chats are getting long, or you've switched tasks multiple times in the same chat, try creating a new chat. Carrying over context from previous tasks is often called "context leakage", and this excess information can muddy the water for Copilot - just the same as for humans.9. Let's practice!
Time to begin automating data transformations and formulas with Copilot!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.