This page has 0 threads | Add post

Exercises

Many people around the world enjoy and benefit from our exercises. If you're one of them - and can afford to - please make a small donation to help keep this and future blogs, exercises, skills assessment tests, tips, tutorials and videos freely available to everyone!

You can find other training resources for the subject of this exercise here:

Microsoft Excel classes

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

Software ==>	Excel (128 exercises)
Topic ==>	Querying data (18 exercises)
Level ==>	Average difficulty
Subject ==>	Excel training

Use power Query's import from folder feature to combine multiple files into a single list.

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

Before you can do this exercise, you'll need to download and unzip this file .

Create a new Excel workbook and save it as Music from Folder.xlsx.

Import data from the folder shown at the top of this exercise, choosing to Combine & Transform Data.

We'll need to make some changes to the query.

Use the first file as the sample file and select the Top Ten Albums worksheet.

Choosing a sample file when importing from a folder in Power Query

Click OK when you've selected the worksheet.

When the data appears, make sure you have the UK Top Ten Albums query selected.

Results of importing from a folder in Power Query

You should have several objects and a bit of tidying up to do in the table!

Apply a filter to the Entered (week ending) column to remove any values which begin with Albums.

Applying a filter to a column in Power Query

You might need to change the data type of the column first to get access to the text filters.

When you've removed the text values, change the data type of the column to a date.

Change the data type of the Weeks in top 10 column to a whole number.

Changing a column data type in Power Query

Watch out for rogue bits of text that you might need to replace first!

Change the data type of the Album column to text and replace the odd symbol which appears after many of the album names.

Replace values in a column in Power Query

Remember that you can select a cell to copy a value from the bottom of the query window.

Make sure that each of the other columns has a sensible data type and then close and load the query to a table in a new worksheet.

The results of importing data from a folder in Power Query

The fruits of your labour.

Save and close the workbook.

You can find other training resources for the subject of this exercise here:

Classes in Excel

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

This page has 0 threads

Add a new post

EXERCISE TOPIC

EXCEL EXERCISES

EXCEL EXERCISES

Use power Query's import from folder feature to combine multiple files into a single list.

Head office

London

Manchester