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:

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

Add custom columns in Power Query to calculate new values in a list of tallest buildings.

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 blank Excel workbook and save it as Building Custom Columns.xlsx.

Choose to import data from the Tallest Buildings.xlsx file from the folder shown at the top of this exercise.

Select the Buildings worksheet and load the data into a table in a new worksheet.

Edit the query in Power Query and add a custom column to calculate the height of each building in feet. You can do this by multiplying the Height m column by 3.28.

Addinga custom column in Power Query to convert metres to feet.

Use this formula to calculate the new value.

Change the data type of the column to a decimal number and move it next to the Height m column.

A custom column in Power Query to convert metres to feet.

The results of the new column.

Edit the formula to round the answer to two decimal places using the Number.Round function.

Editing a custom column formula in Power Query

Edit the existing custom column step rather than adding a new one.

Check the new results.

Results of a custom column in Power Query

The rounded results.

Apply transforms to the Floor column to create separate Floors above ground and Floors below ground columns. Replace any null values in the Floors below ground column with 0.

Make sure there are no null values in the Floors below ground column.

Add a custom column to calculate the total floors for each building by adding Floors above ground and Floors below ground together. You can either write a custom formula or use the Standard | Add method from the Add Column tab in the ribbon.

Choosing a basic calculation template to create a new column in Power Query

Don't forget to select the columns you want to add together first.

Move the new column next to the columns it references.

The results you should see.

Add a conditional column called Has basement. If the value of the Floors below ground column equals 0 then return false, otherwise return true.

You can change the data type of the column to True/False.

Add a new conditional column called Size which tests the value of the Height m column using the conditions and answers in the table below:

Height m	Answer
Less than 400	Small
Less than 600	Medium
Else	Large

Check that your conditions work correctly.

Results of a conditional column in Power Query

Results from the top of the table.

Modify the conditional column to add a condition which tests if the building name contains Trump and, if so, return the answer Bigly.

A modifed conditional column in Power Query

You'll need to make sure this condition is the first one in the list.

Close and load the query then save and close the Excel file.

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

Training courses in Microsoft 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

Add custom columns in Power Query to calculate new values in a list of tallest buildings.

Head office

London

Manchester