Ravindra BagaleCourses & study guides Track your progress
Hard challenge

Hard: Power Query step order

By · Free ·

Absolute refsNumbers as textLeading spacesFilter left onChart range% vs decimalDuplicate headerROUND vs displayFreeze panesPaste valuesVLOOKUP & textVLOOKUP col indexHLOOKUP TRUESUMIF spacesCOUNTIFS ORIF nest orderDate serialINDEX MATCHWildcard COUNTIFMixed currencyCircular & datesCircular interest3D sum renameNamed range staleCF vs IFPivot staleHard-coded NPVValidation movedPQ step orderTyped-over formulaSUM wrong anchorsFiscal year Apr–MarFind all bugsInterview oral

Broken import still has a "null" text row and text Amounts — COUNTA=4. Fixed clean data COUNTA=3 (LibreOffice verified). Order: Promote headers → Change Type → filter nulls.

About this data: Made-up CRM export for Aditi Shah (Surat) and Paul Okeke (Lagos). Practice only.
Download broken workbook Download fixed (after you try)

Why → What → How

Why: Filter before type-change treats nulls as text and lets junk through.

What: Static teaching result of bad vs good PQ order.

How:

  1. Read the step-order note.
  2. Compare Import rows.
  3. Remove null row; coerce Amount.
  4. COUNTA=3.

Ravindra Bagale’s Tip

Change Type early so filters see real nulls/numbers.

Bugs explained (with mini examples)

Bug 1 — Filter before type

"null" string survives.

Bug 2 — Amount as text

SUM fails.

Practice tasks (12)

  1. Task 1. Broken COUNTA?
    Hint: 4.
  2. Task 2. Fixed?
    Hint: 3.
  3. Task 3. Junk row?
    Hint: null name.
  4. Task 4. Ideal order?
    Hint: Headers→Type→Filter
  5. Task 5. Interview.
    Hint: PQ steps wrong order.
  6. Task 6. Coerce Amount.
    Hint: Number.
  7. Task 7. Why text null bad.
    Hint: Not real null.
  8. Task 8. Replace Errors.
    Hint: Optional.
  9. Task 9. Document recipe.
    Hint: A5.
  10. Task 10. Bonus profile.
    Hint: PQ.
  11. Task 11. SUM after fix.
    Hint: 10300.
  12. Task 12. Save.
    Hint: Yes.

In-browser formula check

Type the fixed formula you would put in the named cell. We check formula text — open the workbook for full recalculation.

Step-by-step solution (try first)
  1. Apply correct PQ order / clean sheet.
  2. COUNTA 3. LO: 4 → 3.

Quick quiz

Q1. Filtering before Change Type can leave:
Q2. Fixed COUNTA:
Q3. Broken COUNTA:
Q4. Good early step after headers:
Q5. Amount as text breaks: