Stop Wasting Hours in Power Query: Merge and Analyze Student Data with AI

Problem background & pain points

Imagine you're a course administrator, a corporate trainer, or a teacher. You've just run two separate tests or training sessions, and the results are stored in two different Excel tables, let's call them Test1 and Test2.

Each table contains essential student information: first name, last name, date of birth, the date they joined the program, and their score on that specific test.

Here’s the challenge: you need to create a consolidated report, but only for the students who participated in both tests. Manually cross-referencing names between two long lists is tedious and prone to errors. But the work doesn't stop there. For your final report, you need to:

• Combine the first and last names into a single, consistently formatted "Last Name, First Name" column.
• Calculate each student's current age based on their date of birth.
• Fix a "Date Joined" column that Excel isn't recognizing as a proper date.
• Combine the scores from both tests to get a "Total Score".
• Assign a "Pass" or "Fail" status based on whether the total score meets a certain threshold.

Doing this with traditional Excel functions like VLOOKUP or INDEX/MATCH can quickly become a tangled mess of formulas. A more advanced user might turn to Power Query, but that introduces its own set of complexities.

Traditional Excel Solution: The Power Query Method

For experienced data analysts, Power Query is the go-to tool for this kind of task. It's a powerful data transformation engine built into Excel that can handle merging, cleaning, and reshaping data without writing cell-based formulas. However, it's far from a simple "click-and-go" solution.

Here’s a high-level overview of the steps required to solve our student data problem using Power Query:

1. Load Data into Power Query: First, you need to import both tables (Test1 and Test2) into the Power Query Editor. They are often loaded as "connection only" queries to avoid cluttering your workbook until the final result is ready.

2. Merge Queries: The core step is merging the two queries. You'd select the Test1 query, choose "Merge Queries," and then select Test2 as the table to merge with. To find students who took both tests, you must perform an Inner Join, matching the tables on both the "First Name" and "Last Name" columns.

3. Expand and Clean Columns: After the merge, a new column containing nested table data appears. You need to "expand" this column to pull in the test score from the second table. Then, you can remove the redundant name columns from the second table.

4. Add Custom Columns for New Data: This is where the manual formula work begins within Power Query. You'll need to:

   • Create Full Name: Add a Custom Column with a formula like [LastName] & ", " & [FirstName] to create the formatted name.
   • Calculate Age: Add another Custom Column. The M language formula to calculate age in years can be tricky, something like Duration.Days(Date.From(DateTime.LocalNow()) - [Date of Birth]) / 365.25. You then have to format this as a whole number.
   • Calculate Total Score: Add a simple column that sums the scores from Test1 and Test2.

5. Transform Data Types: You must manually select the "Date Joined" column and change its data type to "Date" to fix the formatting issue. Similarly, ensure numerical columns are recognized as numbers.

6. Add Conditional Column: To create the "Pass/Fail" status, you use the "Conditional Column" feature. You'd set up a rule: if "Total Score" is greater than or equal to 140, output "Pass"; otherwise, output "Fail".

7. Load to Worksheet: Finally, after all these steps, you click "Close & Load" to output the clean, consolidated table into a new Excel worksheet.

The Limitations of the Traditional Approach

While effective, the Power Query method is fraught with challenges for the average Excel user:

• Steep Learning Curve: Understanding concepts like "Inner Join," "Left Outer Join," and the M formula language is not intuitive.
• Rigid and Time-Consuming: The process involves a dozen or more clicks and several manually created formulas. If you make a mistake, you have to go back into the editor and troubleshoot the "Applied Steps."
• Poor Flexibility: What if your manager asks a follow-up question, like "Can you also show me the average score by the month they joined?" You have to go back into the Power Query Editor and add more complex grouping and transformation steps.
• Difficult to Collaborate: Handing this query over to a colleague who doesn't know Power Query is a non-starter. The logic is hidden away in the editor, making it difficult to audit or modify.

The New Solution: Using Excel AI (with Excelmatic)

Instead of navigating a maze of menus and formulas, what if you could just tell Excel what you want? That's the promise of an Excel AI Agent like Excelmatic. It replaces the complex, multi-step workflow with a simple, conversational interface.

Here’s how you can accomplish the exact same task in a fraction of the time.

Step 1: Upload Your Excel File

First, simply drag and drop your Excel workbook containing the Test1 and Test2 tables directly into Excelmatic. The AI will automatically parse the file and recognize the different tables within it.

Step 2: Describe Your Goal in Plain Language

This is where the magic happens. Instead of performing manual steps, you just chat with the AI. You can break down your request into a few simple prompts.

Here's an example of a conversation you could have with Excelmatic to solve the problem:

Step 3: Review and Refine

Excelmatic will instantly display the resulting table. You can see all the transformations applied correctly. The best part is the flexibility. If you have a follow-up request, you don't need to start over. You can just continue the conversation.

For example, you could ask:

• "Now, sort this table by 'Total Score' from highest to lowest."
• "Can you create a pie chart showing the percentage of students who passed vs. failed?"
• "Filter this list to only show students who joined in 2023."

Each command is executed instantly, allowing for a fluid, exploratory data analysis process that is impossible with rigid tools like Power Query.

Step 4: Download Your Results

Once you're satisfied, you can download the final, perfectly formatted Excel file with a single click. You can also copy the generated formulas or table to use in your existing workbooks.

Traditional vs. Excelmatic: A Quick Comparison

FAQ

1. Do I need to be an Excel expert to use Excelmatic for this? Not at all. The entire point of Excelmatic is to empower users who don't know advanced functions or Power Query. If you can describe your business logic in language, you can use the tool.

2. Is my student data secure when I upload it to Excelmatic? Yes. Excelmatic is designed with data security in mind, employing industry-standard encryption and privacy protocols. Your data is processed for analysis and is not stored long-term or shared. For detailed information, always refer to the official privacy policy.

3. What if my column names are inconsistent, like 'First_Name' in one table and 'FirstName' in another? The AI is generally smart enough to recognize these minor variations. However, for best results, you can be explicit in your prompt, such as: "Merge the tables by matching 'First_Name' from Test1 with 'FirstName' from Test2."

4. Can Excelmatic handle really messy data, like inconsistent date formats? Yes, Excelmatic's AI is trained on a vast number of data-cleaning tasks. It can often recognize and convert various non-standard date and number formats automatically, a task that often requires specific formulas or steps in traditional Excel.

5. Can I get the Power Query M code from Excelmatic? No, Excelmatic is a distinct platform that performs the analysis directly. It doesn't generate code for other tools. It provides the final output—the clean data table, a pivot table, a chart, or a formula—that you can download and use immediately.

Take Action: Upgrade Your Excel Workflow Today

Stop spending valuable time wrestling with complex interfaces and obscure formulas. The task of merging and analyzing student data is a perfect example of where traditional methods create unnecessary friction. While Power Query is a robust tool, it's overkill for many day-to-day business users.

By embracing an Excel AI agent, you can shift your focus from how to perform the analysis to what questions you want to answer. You can get your reports done faster, with fewer errors, and with the flexibility to explore your data in ways you never thought possible.

Ready to see for yourself? Try Excelmatic for free and upload one of your own multi-sheet Excel files. Use the prompts from this article as a starting point and watch your tedious data tasks get done in seconds.