Power QueryExercises

Power Query interview questions

Questions analysts get asked, with the structure of a strong answer. Practise explaining your own approach; don't memorise scripts. This is preparation, not a guarantee of any hiring outcome.

For hiring managers

The questions below work as a structured interview: ask the same ones of every candidate and listen for the points listed under each answer, such as whether they mention refresh, data types and how they would check their result. Explanations show understanding; a short practical task shows skill. For the practical part, the timed assessments give a candidate realistic messy data and grade the query against hidden data, and the practice packs provide files you can use for a take-home task in Excel or Power BI.

Excel analyst

  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

All excel analyst questions →

Reporting and operations analyst

  1. technical

    How would you combine twelve monthly CSV exports into one table?

    A strong answer covers

    • Point a query at the folder, filter to the right extension and name pattern (skipping stray or temporary ~$ files), and parse each file's Content with the same steps.
    • Keep the file name (Source.Name) so rows can be traced.
    • Expand the parsed tables, then set types once on the combined result.
    • New months are picked up automatically on refresh.

    Avoid

    • Importing each file separately and appending by hand
    • Losing the file name
  2. data quality

    What checks would you build into a recurring report so problems surface early?

    A strong answer covers

    • Row-count and total checks compared with the previous run or the source system.
    • Anti joins to find keys missing from master data.
    • Keep Errors views or flags for values that failed conversion.
    • Schema checks: fail loudly if a required column is missing rather than silently producing blanks.

    Avoid

    • Relying on someone noticing a wrong chart
    • Suppressing all errors
  3. debugging

    Totals in your report are higher than the source system. Where do you look first?

    A strong answer covers

    • Duplicates: a merge with non-unique keys, or the same file combined twice.
    • Filters: cancelled or test records not excluded.
    • Subtotal or grand-total rows imported from a report-style export.
    • Compare row counts step by step to find where the inflation starts.

    Avoid

    • Adjusting numbers manually
    • Assuming the source system is wrong without checking
  4. prioritisation

    Three teams want report changes this week. How do you prioritise?

    A strong answer covers

    • Clarify impact and deadline for each: who is blocked, what decision depends on it.
    • Separate quick fixes from changes that need data owners.
    • Agree priorities with stakeholders openly and communicate timelines.
    • Protect correctness: don't ship a fast change that breaks existing reports.

    Avoid

    • First come, first served
    • Saying yes to everything
  5. technical

    Explain Fill Down and when you'd use it.

    A strong answer covers

    • Fill Down replaces nulls with the nearest value above.
    • Use it for report-style exports where a group label is printed once per block.
    • Apply it before sorting or filtering so rows are still in report order.
    • It fills null only, not empty text.

    Avoid

    • Using it after sorting
    • Confusing it with Replace Values

All reporting and operations analyst questions →

Junior Power BI analyst

  1. M vs DAX

    What's the difference between Power Query (M) and DAX?

    A strong answer covers

    • Power Query shapes and cleans data before it's loaded; it runs at refresh time.
    • DAX calculates measures and columns on the loaded model and responds to filters in reports.
    • Rule of thumb: row-level cleanup and reshaping in Power Query, aggregations that depend on report filters in DAX.
    • Pushing cleanup into Power Query keeps the model smaller and simpler.

    Avoid

    • Saying they're interchangeable
    • Doing text cleanup in DAX calculated columns by default
  2. technical

    What is query folding and why does it matter?

    A strong answer covers

    • Folding translates Power Query steps into the source's query language so the source does the work.
    • It reduces data transferred and refresh time, especially from databases.
    • Filters, column selection and simple joins usually fold; some steps like index columns often break it.
    • You can check with View Native Query or step folding indicators. File sources such as CSV and Excel never fold.

    Avoid

    • Confusing folding with collapsing steps
    • Claiming everything always folds
  3. technical

    When would you merge in Power Query versus create a relationship in the model?

    A strong answer covers

    • Relationships keep tables separate and let DAX filter across them; they suit star schemas.
    • Merge when you need columns physically in one table, e.g. to filter rows by a lookup attribute before loading.
    • Avoid merging large fact tables with dimensions just to flatten them; relationships are usually better.

    Avoid

    • Always merging everything into one table
    • Never considering model size
  4. data quality

    How do you handle values that fail type conversion during refresh?

    A strong answer covers

    • Decide per case: remove rows, replace errors with null, or keep and flag them.
    • Use try … otherwise for targeted conversions with a fallback.
    • Report error counts so the data owner can fix the source.
    • Don't replace errors with 0 if that looks like a real value.

    Avoid

    • Letting refresh fail without investigation
    • Silently zeroing errors
  5. debugging

    You get a formula firewall error when combining a file with a web API. What does it mean?

    A strong answer covers

    • Privacy levels prevent data from one source flowing into another source's requests.
    • Restructure: separate staging queries for each source, then combine.
    • The firewall also triggers when one step both reads a source and uses another query's result, even with correct privacy levels; separating reading from combining fixes that too.
    • Set accurate privacy levels; ignoring them removes the protection.

    Avoid

    • Turning off privacy levels as the first fix

All junior power bi analyst questions →

Finance and data analyst

  1. technical

    An export writes amounts like 1.234,56. How do you load them correctly?

    A strong answer covers

    • Convert with the source culture, e.g. Change Type with locale de-DE.
    • Don't replace commas and dots by hand; thousands separators break that.
    • Check a sample of large values after conversion.

    Avoid

    • Find-and-replace on separators
    • Using the machine's default locale
  2. data quality

    How would you reconcile bank transactions against the ledger in Power Query?

    A strong answer covers

    • Standardise keys first: dates, amounts rounded to cents, references trimmed.
    • Use a full outer join, or left and right anti joins, to list unmatched items on each side.
    • Match on a composite key (date + amount + reference) and review partial matches separately.
    • Produce a reconciliation table with status columns for review.

    Avoid

    • Matching on amount alone
    • Not handling duplicates on either side
  3. technical

    How do you calculate each cost centre's share of total spend?

    A strong answer covers

    • Group by cost centre with the sum of spend.
    • Compute the grand total in its own step: List.Sum of the grouped column.
    • Add a share column dividing each total by the grand total.
    • Decide whether shares are fractions or percentages and format accordingly.

    Avoid

    • Typing the total in
    • Multiplying by 100 and then formatting as a percentage too
  4. requirements

    Month-end deadlines are tight. How do you make your Power Query process dependable?

    A strong answer covers

    • Build checks that run on every refresh: totals vs source, row counts, unmatched keys.
    • Keep a documented, ordered step list with clear names.
    • Test the query on last month's data and on a deliberately messy sample before close.
    • Agree cut-off times and what happens if a source arrives late.

    Avoid

    • Manual tweaks each month
    • No validation before publishing numbers
  5. technical

    Explain banker's rounding and when it matters.

    A strong answer covers

    • Number.Round rounds halves to even by default: 2.5 becomes 2, 3.5 becomes 4.
    • Accounting rules may require away-from-zero rounding; specify RoundingMode.AwayFromZero.
    • Round at the agreed point (line or total) to avoid reconciliation differences.

    Avoid

    • Assuming 2.5 always rounds to 3

All finance and data analyst questions →