Excel Challenge 3: Power Query and Pivot Tables

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 these data worksheets

Welcome to Excel challenge #3!

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!

The Challenge

There are two Excel workbooks to download and use in this challenge.

Start working on the Excel challenge with these data worksheets

The sales-report.xlsx workbook is the one to work from. And the goal of this challenge is to create the following two Pivot Tables.

In this workbook, there is only a table named ‘products’. This contains the product code, name, and category.

In a workbook named sales-data.xlsx, there are three tables named North_Sales, South_Sales and East_Sales.

We need to import these three tables into sales-report.xlsx and stack them into one list. These are the only three tables we want from that workbook. Exclude everything else.

Here is a snapshot of one of those tables.

We then need to combine the two tables to get the product ‘Name’ and ‘Category’ columns into the stacked sales table.

We also need to add a column with the region name (taken from the table name) and also a conditional column with the sales grouped into the following categories: 0-49, 50-99, 100-199 and 200+.

Consider that additional sales tables may be added in the future, such as West_Sales, and the solution should continue to work with this new data when clicking Refresh.

The Pivot Tables can then be created from this sales table.

Make sure you have downloaded the sales-report.xlsx and sales-data.xlsx workbooks to take on the challenge.

Start working on the Excel challenge with these data worksheets

The Solution

We hope you´ll enjoy 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.