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
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
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
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
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
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…