# Excel Challenge 4: Analyze Data With Power Query

Alan Murray

#### Join the Excel conversation on Slack

Ask a question or join the conversation regarding Excel challenges on our Slack channel.

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
• Put yourself to the test!

Start working on the Excel challenge with this data worksheet

## The Challenge

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

Start working on the Excel challenge with this data worksheet

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.

Start working with data like a pro

Take the Power Query course today!

#### Join the Excel conversation on Slack

Ask a question or join the conversation regarding Excel challenges on our Slack channel.

Alan Murray

Alan is a Microsoft Excel MVP, Excel trainer and consultant. Most days he can be found in a classroom spreading his love and knowledge of Excel. When not in a classroom he is writing and teaching online through blogs, YouTube and podcasts. Alan lives in the UK, is a father of two and a keen runner. Find him on Linkedin here.