Excel Challenge 4: Analyze Data With Power Query

Welcome to another Excel challenge. This is challenge #4! 

This challenge is designed to test your Power Query skills. There are a variety of techniques required to achieve this challenge. It is inspired by the GoSkills Excel - Basic & Advanced course.

Everything you need to participate in the challenge can be found on this page. To take part:

• Watch the challenge video
• Read the instructions below the video
• Download the Excel worksheet you will use to complete the challenge tasks
• Put yourself to the test!

Want to chat about your approach and process with other Excel-heads? Join our new Slack channel to share your insights and questions with like-minded learners.

The Challenge

There is one Excel workbook to download and use in this challenge.

The workbook contains two tables (Test1 and Test2) and these have both been loaded as connection only queries.

Each table contains information about students. This includes their first name, last name, date of birth, the date that they joined the program, and their test score.

This is the Test1 table.

And this is the Test2 table.

Only a portion of the students attended both tests, and they are our focus in this challenge.

We need to produce the following table, loaded to an Excel worksheet.

These are some of the tasks you will need to accomplish to get to the final solution.

• Produce a query/table which only includes the students who took both tests. For this example, the students are uniquely identified by using both their first and last names.
• Create a column in the format last name, first name instead of the two name columns.
• Calculate the age in years of each student.
• Fix the Date Joined column which is currently not recognized as a date.
• Include columns for both test scores and create an additional column with the total of both.
• Create a column that displays “Pass” if they achieve a total score of 140 or higher, and “Fail” if they do not.

We hope you'll enjoy this challenge! Don't forget to join the fun on our new Slack channel to connect with other learners and challenge creator Alan Murray.

The Solution

We have a range of expert-led Excel courses for all skill levels that allow you to learn at your own pace. Check out our Basic and Advanced Excel course to learn more essential skills, and our Power Query course for a deeper dive into this powerful tool.