Excel analyst interview prep
Power Query in Excel: cleaning exports, combining files and building refreshable workbooks.
Free questions
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
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
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
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
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…