HumachLearnLearn · explore · apply
Start learning
Excel & Spreadsheet Analytics · Lesson 24

Power Query Fundamentals

Power Query Fundamentals is a spreadsheet-analysis technique.

ConceptWorked examplePracticeKnowledge check
Textbook walkthrough

What Power Query Fundamentals actually means

Power Query Fundamentals is a spreadsheet-analysis technique. The key to reliable spreadsheet work is to make references, ranges, data types and aggregation rules explicit so that copied formulas and refreshed data still mean the same thing.

Power Query Fundamentals matters because spreadsheets are often decision tools as well as calculation tools. A small reference, type or range error can propagate into reports, so formulas and source relationships must remain auditable as data change.

Deeper walkthrough

Read Power Query Fundamentals as a mechanism, not a recipe

Treat this as a sequence of observable decisions rather than one opaque command. Stage 1: Confirm the input cells/table columns and their data types. Stage 2: Write the formula or query with deliberate relative/absolute/structured references. Stage 3: Test the formula on one row or small subset where the answer is known. Final checkpoint: Prefer tables and named/structured references when they make data growth safer.

Mechanism

Follow the transformation

Confirm the input cells/table columns and their data types.

Write the formula or query with deliberate relative/absolute/structured references.

Test the formula on one row or small subset where the answer is known.

Evidence

Know what would convince you

  • Evaluate the formula/query on one row or group where the answer is known manually.
  • Use formula auditing/table totals and inspect references after fill/copy/refresh.
Useful distinctionRelative A2: Changes when copied.
Visual demonstration of Power Query Fundamentals
Visual demonstration: use the diagram to trace the main objects and state changes involved in Power Query Fundamentals.
Click a stage to inspect what happens, what changes, and what should be checked before moving on.
Stage 1

Confirm the input cells/table columns and…

Confirm the input cells/table columns and their data types. This is an input-preparation stage for Power Query Fundamentals. Verify the relevant type, shape, units, keys, missingness or assumptions before later steps depend on them.

State focus: identify exactly what changed at this stage and what observable evidence confirms that change.
How it works

Trace the mechanism step by step

  1. Confirm the input cells/table columns and their data types.
  2. Write the formula or query with deliberate relative/absolute/structured references.
  3. Test the formula on one row or small subset where the answer is known.
  4. Copy/fill/refresh and audit for shifted references, blanks and errors.
  5. Prefer tables and named/structured references when they make data growth safer.
Worked demonstration

Make the concept concrete

Demonstration

Power Query example

Table.TransformColumnTypes(Source, {{"Date", type date}, {"Sales", type number}})
Expected / illustrative result
Date is typed as date; Sales is typed as number. Invalid values surface as conversion errors.
Interpret the result.

For Power Query Fundamentals, connect the displayed result to the specific input and mechanism above; independently verify one value/state change rather than treating successful execution as proof.

Distinctions & related ideas

Know what this is — and what it is not

Relative A2Changes when copied.
Absolute $A$2Stays fixed when copied.
Mixed $A2 / A$2Locks only column or row.
Structured Sales[Revenue]Refers to a table column and expands with the table.
Use deliberately

When it is appropriate

Use Power Query Fundamentals when a spreadsheet provides an inspectable, cell/table-oriented way to calculate or summarise the data and the workbook can remain auditable.

Boundary conditions

When to stop or reconsider

Prefer a database or scripted workflow when the workbook becomes difficult to audit, refresh reproducibly, version, or scale without manual intervention.

Common mistakes

Failure modes to recognise

  • Copying formulas while relative/absolute references shift to unintended cells.
  • Mixing raw inputs, manual overrides and derived results without a visible data-flow convention.
  • Accepting error-free output without testing blanks, duplicated keys, changed table size or refresh behaviour.
Verification

How to check the result

  • Evaluate the formula/query on one row or group where the answer is known manually.
  • Use formula auditing/table totals and inspect references after fill/copy/refresh.
  • Change one source cell deliberately and predict the exact dependent result that should update.
Hands-on practice

Demonstrate understanding

Try this:

Build a tiny, inspectable example of Power Query Fundamentals. First confirm the input cells/table columns and their data types. Then write the formula or query with deliberate relative/absolute/structured references. Write the expected result before running it, and explain one condition that would make the result misleading or invalid.

Use a tiny table and keep source cells separate from calculated cells. Audit every reference in one representative formula before filling it down.
Knowledge check

Check reasoning, not memorisation

Before trusting a result from Power Query Fundamentals, which check provides the strongest evidence that you understand and applied it correctly?

Quick reference

Keep the important distinctions visible

Step 1Confirm the input cells/table columns and their data types.
Step 2Write the formula or query with deliberate relative/absolute/structured references.
Step 3Test the formula on one row or small subset where the answer is known.
Step 4Copy/fill/refresh and audit for shifted references, blanks and errors.
Lesson summary

What to remember

  • Power Query Fundamentals is a spreadsheet-analysis technique. The key to reliable spreadsheet work is to make references, ranges, data types and aggregation rules explicit so that copied formulas and refreshed data still mean the same thing.
  • Confirm the input cells/table columns and their data types.
  • Copying formulas while relative/absolute references shift to unintended cells.
  • Evaluate the formula/query on one row or group where the answer is known manually.