Excel Challenge 54: Clean Up Messy Data With Power Query

Here's a fresh new challenge to strengthen your Power Query skills! 

To get started,

1. Watch the challenge video. 
2. Download the Excel worksheet. 
3. Complete the challenge task.
4. Share your solution, discuss, or ask for help in our Slack channel.

The challenge

Jordan, a newly hired data analyst at Horizon Enterprises, has just stepped into the buzzing world of corporate analytics. The company's quarterly performance data is sprawled across a complex Pivot-style Excel sheet.

The CEO, a strong believer in performance-based rewards, has a bold idea: to award year-end bonuses to the top-performing sales managers based on their annual revenue. But there is one problem — the data is anything but analysis-ready.

The original dataset includes Regions, Managers, Years, Quarters, Months, and Sales Revenues, but all in a Pivot-style layout — perfect for printing, not for analysis. The CEO reaches out to the Data Department with a tight deadline.

I want a clear breakdown of total sales per manager, by year. Use this to determine their bonuses.

Jordan knows this is the perfect time to unleash the power of Power Query.

What you need to do

Help Jordan by doing the following:

• Open the file and explore the Dataset worksheet.
• Use Power Query to transform the pivoted data into a clean, tabular format.
• Load the transformed data into Excel and create a Pivot Table that displays total sales revenues per Manager per Year.
• Make sure your final Pivot Table is dynamic and ready for further filtering or slicing if needed.

Why this Power Query challenge matters

By completing this challenge, you’ll showcase your ability to:

• Unpivot and normalize messy data with Power Query
• Build structured tabular datasets from semi-structured sources
• Create business-ready Pivot Table reports to support executive decisions

This is your chance to demonstrate the skills that data-driven companies rely on every day. Whether you’re prepping for an interview, leveling up your Excel expertise, or just love cracking real-world data puzzles, this challenge is for you.

Hints

Use the following video to get an introduction to Power Query.

Free lesson from GoSkills course: Microsoft Excel - Power Query

And here's another helpful resource: Power Query Tips: 10 Most Useful Commands

The solution

We hope you enjoyed taking part in this challenge!

Stay tuned to the GoSkills Excel Resource hub for more Excel tutorials, and check out our range of expert-led Excel courses for all skill levels to further sharpen your skills.

If you liked this challenge, you’ll love our Power Query course, which will help you learn how to transform, consolidate, and make sense of big, messy data.