Power QueryExercises

Load the named table from an Excel workbook (kite shop)

The workbook has a notes sheet, a printed sales sheet and a proper Excel table. Load the table.

Level
beginner
Time
~6 min
Task
Build the query
Access
Free — no account needed
Loading the practice workspace…

About this exercise

What you'll practise

Navigate a workbook by item name and kind instead of by position.

Northwind Kites sells kites and accessories through three regional stores. Orders are exported from the till system every week. The weekly file is an Excel workbook.

Steps

  1. SalesWorkbook is an Excel file. Excel.Workbook(SalesWorkbook) lists its items with columns Name, Data, Item, Kind and Hidden.
  2. Load the item whose Item is tblSales and whose Kind is Table (not the Sales sheet, which has a title row).
  3. Return that table as it is: columns OrderID, Customer, Qty, UnitPrice.

Your result must

  • Columns: OrderID, Customer, Qty, UnitPrice
  • No title or blank rows
  • Picks the table by name, not by position

Input

  • SalesWorkbook (excel)
    • Sheet "Notes" (2 rows)
    • Sheet "Sales" (15 rows)
    • Table "tblSales" (13 rows)

Expected output columns

OrderID (text), Customer (text), Qty (number), UnitPrice (number)

First 3 of 12 expected rows
OrderIDCustomerQtyUnitPrice
0001Uma Duarte133.35
0002Bao Grieve447.66
0003Jun Castillo936.03