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:

Training classes in Excel

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 ==>	Relatively easy
Subject ==>	Excel training

Use parameters in Power Query to dynamically query worksheets of top ten albums in the UK charts.

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 Top Ten Parameters.xlsx.

Import data from the Top Ten Albums worksheet in the UK Top Ten Albums 2021.xlsx file from the folder shown at the top of this exercise.

Choosing an Excel file to import in a Power Query query

The folder contains multiple Excel files but pick just one for now.

Choose to transform the data and make sure each column has a specific data type.

Columns with assigned data types in Power Query for Excel

The first few columns you should see.

Now we'd like a parameter to make it easy to switch between files in the Source step of the query.

Select the Source step and, in the formula bar, copy the path of the Excel workbook you imported.

Selecting the file path from a source step in Power Query

Your path may be different. If you can't see the formula bar, choose View | Formula Bar from the ribbon.

Create a new text parameter called SourcePath and paste the path you copied into the Current Value property

Creating a parameter in Power Query for Excel

Set up the parameter as shown here.

Edit the Source step of the Top Ten Albums query to use the parameter you've just created.

Using a parameter to provide the file path for a source in Power Query

Set the File path using your parameter.

Edit the value of the parameter to refer to one of the other files in the folder shown at the top of this exercise.

Editing a parameter in Power Query for Excel

You can edit just the end of the file name to refer to a different year.

Check that the query returns different results.

Different results returned by a parameterised query in Power Query

Try other file names to see the results change.

Create a number parameter called PeakChartPosition and set its current value to 1.

Creating a number parameter in Power Query for Excel

We'll use this to filter the query results.

Add a filter to the Peak column to show rows which match the value of the PeakChartPosition parameter.

Using a parameter in a filter in Power Query for Excel

Use the parameter in the Filter Rows dialog box.

Edit the value of the parameter and check that it affects the results returned by the query.

Results of a query filtered using parameters in Power Query for Excel

Enter a value between 1 and 10 for the parameter.

Typing in a number is too much effort, so let's create a list of numbers to pick from. Create a new blank query as shown below:

Right-click in the Queries list and choose the option shown here.

In the formula bar, use the List.Numbers function to create a list of numbers from 1 to 10.

Using the List.Numbers function in Power Query to generate a list of numbers.

Enter this code in the Source step of the blank query.

Rename the query as Chart Positions.

The query returns a list.

Choose to manage parameters and use the new query to set the suggested values for the PeakChartPosition parameter.

Using a query to populate a list of suggested values in a Power Query parameter

Use these settings.

Check that you can pick a value from a dropdown list when you edit the parameter.

Editing parameters in Power Query using a list of selected values.

Much better!

Close and load the query to a table on a new worksheet and then save and close the workbook.

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

Training 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 parameters in Power Query to dynamically query worksheets of top ten albums in the UK charts.

Head office

London

Manchester