Power QueryExercises

Excel analyst interview prep

Power Query in Excel: cleaning exports, combining files and building refreshable workbooks.

Free questions

  1. technical

    What is Power Query, and why use it instead of formulas for cleaning data?

    A strong answer covers

    • Power Query records transformation steps that rerun on refresh, so monthly cleanup becomes one click.
    • Steps are visible and auditable in Applied Steps, unlike formulas scattered across cells.
    • It handles volumes and reshaping (unpivot, merge, combine files) that formulas do awkwardly.
    • If you have one, describe a real task you automated and what it saved, for example a monthly copy-paste of exports replaced by a folder query. Don't invent one.

    Avoid

    • Describing it only as 'importing data'
    • Not mentioning refresh/repeatability
  2. technical

    When would you choose Unpivot Other Columns over Unpivot Columns?

    A strong answer covers

    • When new columns (like next month) should be unpivoted automatically.
    • Unpivot Other Columns names the identifier columns to keep; everything else is unpivoted, including future columns.
    • Unpivot Columns hard-codes today's column names, so new months stay wide after refresh.

    Avoid

    • Saying they're the same
    • Not mentioning refresh behaviour
  3. debugging

    A query that worked last month now fails with 'The column Qty of the table wasn't found'. How do you investigate?

    A strong answer covers

    • Click through the applied steps to find the first failing step.
    • Compare the source's current headers with what the step expects: renamed, removed, case changed, or headers not promoted.
    • Fix at the right place: rename early, update the step, or make the step rule-based if headers legitimately vary.
    • Tell the data owner if the export changed unexpectedly.

    Avoid

    • Deleting the failing step
    • Adding MissingField.Ignore everywhere without understanding the change
  4. data quality

    How do you make sure a merge didn't duplicate rows?

    A strong answer covers

    • Check the row count before and after the merge.
    • Before merging, confirm the lookup key is unique, e.g. Group By the key and count rows greater than 1.
    • If keys repeat legitimately, aggregate or deduplicate the lookup first, or merge on a composite key.

    Avoid

    • Only eyeballing the result
    • Removing duplicates after the merge, hiding the root cause
  5. requirements

    Your manager asks for 'sales by region'. What do you ask before building it?

    A strong answer covers

    • Which sales count: completed only, net of returns, including tax?
    • What period and how often it refreshes.
    • Region definition: billing or shipping region, and how to treat unknown regions.
    • Output format and who uses it, to decide grain and columns.

    Avoid

    • Building immediately with assumptions
    • Asking only about formatting

Loading more questions…