Key Topics & Core Concepts

Assignment Overview & Deliverables

Power Query prefers structured data in the form of an Excel table. In this assignment, you will format the 6,000-row corporate directory dataset as an Excel table, assign formal table properties, ingest the records into Power Query, cleanse and standardize mixed-case name and address strings, and load the transformed dataset back to Excel.

Step-by-Step Instructions

Step 1: Open Starter & Format Data as an Excel Table

  1. Download and open Operations-Directory.xlsx in Excel. Click Enable Editing to begin working.
  2. Save your workbook as [your initials]OperationsDirectory.xlsx.
  3. Click anywhere in the data range.
  4. Turn the data into an Excel table:
    • On the Home tab, in the Styles group, click Format as Table. (Keyboard shortcut: Ctrl + T).
    • In the Create Table dialog box, confirm that the data range displays $A$1:$O$6001. Because there are no blank rows or columns, Excel selects the complete 6,000-record dataset automatically.
    • Check the box for My table has headers, then click OK.

Note from the Professor: If you do not format the data as a table first, when you import the data into Power Query, Excel will automatically turn it into a table for you. Creating it deliberately gives you full control over table naming and range boundaries.

Step 2: Assign Formal Table Properties

  1. With your cursor anywhere inside the newly formatted table, navigate to the Table Design contextual tab (or Table Tools Design).
  2. In the Properties group on the far left, click inside the Table Name box. (Keyboard shortcut: press and release Alt, then press J → T → A).
  3. Type Directory as the table name and press Enter.

Note from the Professor: Remember that Table Names (like named ranges):
• Cannot contain spaces or punctuation symbols.
• Must begin with a letter, underscore (_), or backslash (\).
• Cannot conflict with standard cell references (e.g., C13).

Step 3: Ingest Table into Power Query

  1. Ensure your active cell remains inside the Directory table.
  2. On the Data tab, in the Get & Transform Data group, click From Table/Range (or From Sheet).
  3. The Power Query Editor will open in a separate window displaying all 6,000 rows across 15 columns.
  4. In the Query Settings pane on the right under Properties, verify that the query is named Directory.

Step 4: Cleanse, Standardize & Transform Fields

  1. Select the text columns with mixed casing (such as First Name, Surname, Street, and City).
  2. On the Transform tab, in the Text Column group, click Format > Capitalize Each Word to convert inconsistent uppercase and lowercase entries into standard Title Case.
  3. With the text columns still selected, click Format > Trim to strip any rogue leading or trailing whitespace characters.
  4. Hold Ctrl to select First Name, Middle Initial, and Surname in order. On the Add Column tab, click Merge Columns. Set the separator to a Space and name the new column Full Name.
  5. Review the data type indicators in the column headers (e.g., ABC for Text, 123 for Whole Number, Calendar icon for Date). Ensure ZIP/postal codes and phone numbers are formatted as Text so leading zeros are preserved.

Step 5: Close & Load to Excel & Canvas Submission

  1. On the Home tab of the Power Query Editor, click the upper half of the Close & Load button.
  2. Power Query will process all steps and load the cleaned records into a new worksheet tab in your workbook.
  3. Confirm that all 6,000 records loaded successfully without query errors in the Queries & Connections side pane.
  4. Save the finalized workbook as [your initials]OperationsDirectory.xlsx.
  5. Perform an autograder pre-flight check at /teaching/ISMG-2050/submit.html.
  6. Upload your completed workbook directly to Canvas before the deadline.

Refer to Canvas for official submission cutoff and penalty policies.

Key Skills Evaluated