Power QueryExercises

Junior Power BI analyst interview prep

The transformation layer in Power BI: shaping data for a model and knowing where M ends and DAX begins.

Free questions

  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

Loading more questions…