Excel Challenge 2: Clean and Transform Data with Power Query

Welcome to Excel challenge #2!

This week's challenge is designed to test your knowledge of the Power Query concepts from 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.

Excel Challenge #2

You have received a file with resolved issues data.

It contains the following columns:

• The area from which the issue was received
• A combined column with the name of the person assigned to the issue and the issue date
• The priority level of the issue
• The number of days that it took to resolve the issue

Unfortunately the data is not in a format that makes it easy to analyze.

There are various problems such as:

• The area code is in a row on its own
• The name and date is in a combined column and requires separating
• Some of the names have the incorrect case
• There are average rows and blank rows under each area which we do not need

The challenge is to use Power Query to transform this data into a format that we can analyze easily.

And then answer a couple of questions about the data.

We want to know:

1. The average response time in days

2. The number of level 1 or level 2 issues that were resolved in less than 5 days

Space has been provided between the issues data and the questions to load the Power Query table.

Test data is provided in range L12:O20 so that you can check if your solution works when new rows are added to the issues data.

Download the challenge workbook to take on the challenge.

The Solution

We hope you enjoyed taking part in this challenge! Stay tuned to the GoSkills Excel Resource hub for more Excel challenges, and check out our range of Excel courses to further sharpen your skills.