Power Query: Repeatable Data Import and Cleaning

1. Introduction

Power Query records a sequence of data preparation steps that can be refreshed when a source file changes. It reduces repetitive copy-and-paste work and exposes transformations for review.

2. Understand the concept

Each applied step transforms a table. Set types deliberately, remove only understood errors, and preserve the source fields needed for audit. Append stacks compatible tables; merge joins tables by keys. Validate row counts and unmatched keys after each operation.

3. Syntax and interpretation

Source → Promote headers → Set types → Filter rows → Merge queries → Load result

The order of steps matters: converting a field before filtering can reveal invalid values that should be inspected.

4. Apply it to an analysis

Create refreshable import steps for type conversion, filtering, appending, and merging spreadsheet data. Start with a small, known example and state what each row or value represents. After the transformation, check row counts, types, keys, and units against the original data. If an assumption fails, stop and investigate instead of presenting the output as a valid result.

5. Common mistakes

• Removing error rows without counting or inspecting why conversion failed.
• Merging on a non-unique lookup key and creating duplicate output rows.

6. Practice task

A refresh produces 20 unmatched product SKUs. What should you do before publishing the report?

7. One possible solution

Inspect and report the unmatched keys, confirm whether the lookup table is stale or the source values need normalization, and reconcile output row counts before publishing.

8. Summary and next step

Create refreshable import steps for type conversion, filtering, appending, and merging spreadsheet data. Continue to the next course section and apply this foundation to a different analytics tool or workflow.