aston

Introduction

This is a case exercise or case study page for anyone who is interested in using Power Query wither that is in Excel or Power BI. I have created the following exercise as a Power BI file but it will also work n Excel Power Query. The screenshots below all come from Power BI.

Power BI Exercise Using Aston Martin Second Hand Car Prices and Other Details

You are given the file aston_martin_second_hand_oct_2018_original.xlsx to work with and this file contains my original data downloads and a cleaned up version of those data in the Excel Table called aston_used.

Tasks

Open a new, blank Power BI file

Get Data from Excel

Locate the aston martin Excel file and select the aston_used table

Click Load

You now have your first query and we will use that throughout this exercise

Create New Queries

Duplicate the aston_used query four times:

  • aston_used_exterior: delete all columns except Exterior, delete duplicates and then create an index column starting at 1
  • aston_used_interior: delete all columns except Interior, delete duplicates and then create an index column starting at 1
  • aston_used_model: delete all columns except Model, delete duplicates and then create an index column starting at 1
  • aston_used_transmission: delete all columns except Transmission, delete duplicates and then create an index column starting at 1

Page 1

Create the following visualisations

LINE Charts

Price by Mileage and format it then copy it twice

  • Mileage by Age
  • Price by Age

Change the data colours by visualisation

Page 2

COLUMN Charts

Average Price by

  • Exterior Index
  • Interior Index
  • Model Index
  • Transmission Index

Change the data colours by visualisation

Page 3

COLUMN Charts

Count of Age by

  • Exterior Index
  • Interior Index

Average Price by Interior Index

SCATTERGRAPH

Exterior Index v Interior Index

Change the data colours by visualisation

Page 4

COLUMN Charts

Count of Age by

  • Model Index
  • Transmission Index

TABLE

Transmission and Count of Age

 

This is a beginner to intermediate level exercise I think but good luck with it whatever your level.

Here is the Excel file to start you off: aston_martin_second_hand_oct_2018_original

 

Duncan Williamson 17th October 2018