# Excel Challenge 2: Clean and Transform 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.

Start working on the Excel challenge with this data worksheet

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

## Excel Challenge #2

Start working on the Excel challenge with this data worksheet

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.

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.

Start working on the Excel challenge with this data worksheet

Need a hint? Check out this lesson in our Excel Basic & Advanced course, or this one from our Power Query course.

## 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.

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.