How should I prepare for a data analyst case task?

Restate the question, check data quality, answer with two or three clear findings, list assumptions and say what you would check next.

Data Analyst Interview Questions with Answers

Excel questions

Difference between VLOOKUP and XLOOKUP? XLOOKUP can look left, defaults to exact match, returns a custom value when not found and handles ranges more safely. VLOOKUP needs a column index and only looks right. See XLOOKUP in Excel.

How would you find duplicates? Conditional formatting, Remove Duplicates, COUNTIFS, or Power Query; mention checking which column defines a duplicate.

What is a pivot table used for? Summarising large tables by categories without formulas; mention refresh and grouping. See pivot tables.

Data cleaning questions

You receive a file with blanks and inconsistent dates. What do you do? Profile first, identify business rules, fix in a repeatable way (Power Query), keep the raw data untouched and document assumptions.

Power BI questions

Measure vs calculated column? A measure is computed at query time in filter context; a calculated column is computed at refresh and stored per row. Prefer measures for aggregations.

What is a star schema? Fact tables for events and dimension tables for descriptions, joined by one-to-many relationships. See Power BI data modeling.

What does CALCULATE do? It evaluates an expression in a modified filter context. See DAX basics.

Case-style questions

“Sales dropped 10%. How do you investigate?” Confirm the number, split by time, product, region and customer, check data issues, compare with seasonality and report what you found and what you would test next.