Data Analyst (Power BI) Interview Questions &amp; Answers

Show answer

I would create a mobile layout in Desktop through View, Mobile layout, placing the most important cards and charts in a single column with larger fonts. Remove or simplify dense tables, check slicer usability on touch screens and test in the Power BI mobile app. If mobile use is common, I might design a separate concise summary page for mobile users.

What is Power Query and where is it used?

Show answer

Power Query is the data connection and transformation engine in Power BI, Excel, dataflows and Fabric Data Factory. It lets you connect to hundreds of sources and shape data with a recorded series of steps, such as removing columns, changing types, merging and unpivoting. Behind the interface each step is written in the M language. Steps re-run automatically on every refresh, making cleaning repeatable.

Write basic measures for total sales, total cost and profit margin percentage.

Show answer

Total Sales = SUM(Sales[Amount]) Total Cost = SUMX(Sales, Sales[Qty] * Sales[UnitCost]) Profit = [Total Sales] - [Total Cost] Margin % = DIVIDE([Profit], [Total Sales]) Format Margin % as a percentage. Building measures on other measures keeps logic in one place and easy to change.

Explain row context and filter context.

Show answer

Row context means DAX is evaluating one row at a time, which happens in calculated columns and iterators like SUMX, so you can refer to column values of the current row. Filter context is the set of filters applied to the model from slicers, visual rows, columns and CALCULATE. Measures are evaluated in filter context. Row context does not filter the model unless converted by context transition.

How do you handle errors in Power Query without failing the whole refresh?

Power Query and Data TransformationMedium

Show answer

Use Keep Errors on a copy of the query to inspect the bad rows, then fix the cause, such as wrong data types or invalid dates. Replace Errors can substitute a value, and in M try ... otherwise catches errors per value: = Table.AddColumn(Source, "AmountNum", each try Number.From([Amount]) otherwise null, type nullable number) Log error rows to a separate query so they can be reviewed rather than silently dropped.

What is the difference between drill-down and drill-through?

Show answer

Drill-down moves within the same visual through a hierarchy, such as Year to Quarter to Month, using the drill buttons. Drill-through moves the user to a separate detail page filtered by the selected item, such as right-clicking a customer to open a Customer Details page. Drill-through pages are set up by adding fields to the drill-through well and include a back button.

Write a measure for sales of a specific category and region.

Show answer

Electronics North Sales = CALCULATE( [Total Sales], 'Product'[Category] = "Electronics", 'Store'[Region] = "North" ) Multiple filter arguments are combined with AND. Wrap a filter with KEEPFILTERS if it should intersect with an existing slicer selection instead of replacing it.

What are report themes and why use them?

Show answer

A report theme is a JSON file that sets default colours, fonts, visual styles and backgrounds for all visuals. Using a company theme keeps reports consistent with brand guidelines and saves formatting time, since new visuals adopt it automatically. Themes can be built in Desktop's theme customiser or edited as JSON and shared across the team as a standard.

How do you design a KPI card that shows value, target and variance?

Show answer

Use the new Card visual or a KPI visual with a main measure such as Total Sales, a target measure and a variance percent measure. Add conditional formatting so the variance turns red below target and green above it, and include a small trend if space allows. Keep labels short, such as Sales vs Target, and show the comparison period in the subtitle.

What is the difference between Power Query M and DAX?

Show answer

M is the language of Power Query, used at refresh time to extract, clean and shape data before it enters the model, such as merging tables or splitting columns. DAX is used after data is loaded, to define measures, calculated columns and security that evaluate in filter context at query time. As a rule, do row-level shaping in M or the source and dynamic calculations in DAX.

Leadership wants to see a red, amber, green status for each region against target. How do you build it?

Show answer

I would create a measure for achievement percentage, DIVIDE(Sales, Target), and a status measure returning a colour or icon based on thresholds agreed with leadership, such as below 90 percent red, 90 to 100 amber, above 100 green. Apply it through conditional formatting in a matrix or as icon rules. I would also add text or icons so status is clear without colour.

Why should you set data types correctly in Power Query?

Show answer

Correct data types make sorting, filtering, aggregation and relationships work properly; text-typed numbers cannot be summed and mismatched key types break relationships. Types also affect compression and model size, for example whole numbers compress better than decimals or text. Set types once near the end of a query and avoid repeated automatic Changed Type steps, which can fail when column names change.

What is the difference between ALL, ALLEXCEPT and REMOVEFILTERS?

Show answer

ALL returns all rows of a table or column ignoring filters, and as a CALCULATE argument it removes filters, useful for percent of total. REMOVEFILTERS does the same removal but only as a CALCULATE modifier and reads more clearly. ALLEXCEPT removes all filters from a table except the listed columns, such as keeping the Year filter while removing others. ALLSELECTED keeps filters from outside the visual, like slicers.

Why should you use DIVIDE instead of the division operator?

Show answer

DIVIDE(numerator, denominator, alternate) handles division by zero and blank denominators safely, returning blank or the alternate value instead of an error or infinity. It makes measures like margin percentage cleaner without IF checks. The division operator is slightly faster when you are certain the denominator is never zero, but DIVIDE is the safer default in reports.

Write a measure for percentage of grand total sales.

Show answer

Sales % of Total = DIVIDE( [Total Sales], CALCULATE([Total Sales], REMOVEFILTERS('Product')) ) This gives each product's share of total across all products. Use ALLSELECTED('Product') instead if the denominator should respect slicer selections on products.

Every month a new Excel file arrives in a folder. How do you load all of them automatically?

Show answer

Use the Folder connector, or SharePoint Folder for files on SharePoint, pointing to the folder, filter to the right file extension and naming pattern, then use Combine Files. Power Query creates a sample-file transform function applied to every file and appends the results. New files dropped into the folder are included on the next refresh, as long as their structure matches the sample.

What are variables in DAX and why use them?

Show answer

VAR defines a named value inside an expression and RETURN gives the final result. Variables make measures easier to read, avoid repeating the same calculation, which improves performance, and help debugging because you can return an intermediate variable. A variable is evaluated once in the context where it is defined, so its value does not change if later CALCULATE filters are applied.

How do you set up drill-through to a customer details page?

Show answer

Create a new page named Customer Details, add visuals showing customer information, orders and trends, and drag Customer Name or Customer ID into the Drill through well in the Visualizations pane. Power BI adds a back button automatically. Users can now right-click a customer in any visual and choose Drill through, Customer Details, and the page opens filtered to that customer.

What is context transition?

DAX FundamentalsHard

Show answer

Context transition happens when CALCULATE, or a measure reference which implicitly wraps CALCULATE, is evaluated inside a row context. The values of the current row are converted into filters on the model. For example, in a calculated column on Customer, [Total Sales] returns that customer's sales because the row becomes a filter. It is powerful but can be slow over large tables and surprising with duplicate rows.

A measure shows the same value on every row of a table visual. Why?

Show answer

The measure is probably ignoring the filter context of the rows, for example using ALL on that column or table, or the row field comes from a table that is not related to the fact table, so its filter cannot reach the data. I would check relationships in model view, the direction of filtering, and any ALL or REMOVEFILTERS in the measure.

What makes a good dashboard design?

Show answer

A good dashboard answers specific questions for a specific audience. Put the most important KPIs at the top left, follow a logical flow from summary to detail, use a few consistent colours with one highlight colour, and choose simple visuals like bar and line charts. Remove clutter such as 3D effects and heavy borders, label clearly, show context like targets or last year, and keep each page to a manageable number of visuals.

What do RELATED and RELATEDTABLE do?

Show answer

RELATED fetches a value from the one side of a relationship into a row context on the many side, such as RELATED('Product'[Category]) in a Sales calculated column. RELATEDTABLE goes the other way, returning the related rows from the many side for the current row, often wrapped in COUNTROWS to count a customer's orders. Both need an existing relationship and a row context.

How do you split a full name column and clean text in Power Query?

Show answer

Select the column, use Transform, Split Column by Delimiter (space, leftmost) to get first and last names. Then use Format, Trim and Clean to remove extra spaces and non-printable characters, and Capitalize Each Word for consistent case. In M: = Table.TransformColumns(Source, {{"Name", each Text.Proper(Text.Trim(_)), type text}})

What is the difference between Merge Queries and Append Queries?

Show answer

Merge is like a SQL join: it combines columns from two tables based on matching key columns, with join kinds like left outer, inner, full outer, left anti and right anti. Append stacks rows of tables with the same structure on top of each other, like a SQL UNION ALL, for example combining monthly sales files. Merge widens a table, append lengthens it.

How do you mark a table as a date table and why?

Data ModellingEasy

Show answer

In Table view or Model view, select the date table, choose Table tools, Mark as date table, and select the date column, which must be unique, contiguous and without blanks. This tells Power BI to use it for time intelligence and disables the auto date hierarchy for that table. Also turn off Auto date/time under Options, Data Load to avoid hidden date tables for every date column.

How do you choose the right chart type in Power BI?

Show answer

Match the chart to the question. Use bar or column charts to compare categories, line charts for trends over time, stacked bars for part-to-whole across few categories, scatter charts for relationships, maps only for true geographic patterns, cards and KPI visuals for single numbers, and tables or matrices when users need exact values. Avoid pie charts with more than four or five slices.

What is DAX?

Show answer

DAX, Data Analysis Expressions, is the formula language for Power BI, Analysis Services and Power Pivot. It is used to create measures, calculated columns, calculated tables and security rules. DAX looks like Excel formulas but works on tables and columns with filter context. Key functions include SUM, CALCULATE, FILTER, ALL, RELATED, time intelligence functions and iterators like SUMX.

Tell me about a time a user rejected your report design. What did you do?

Visualisation and Report DesignMedium

Show answer

Situation: sales managers stopped using a dashboard I built. Task: find out why. Action: I watched two managers use it and found they needed weekly target gaps first, which were hidden on page three. I moved those to the top, removed four rarely used charts and added drill-through for details. Result: weekly usage in the activity log tripled within a month.

How do you add a conditional column in Power Query?

Show answer

Use Add Column, Conditional Column and define rules, or write M directly: = Table.AddColumn(Source, "Band", each if [Amount] >= 100000 then "High" else if [Amount] >= 25000 then "Medium" else "Low", type text) Adding the type at the end avoids an extra Changed Type step. Conditional columns built here are computed at refresh, not at query time.

What does CALCULATE do?

Show answer

CALCULATE evaluates an expression in a modified filter context. Its filter arguments can add, replace or remove filters, for example CALCULATE([Total Sales], 'Product'[Category] = "Electronics") replaces any category filter with Electronics. It also performs context transition when used inside a row context, turning the current row into an equivalent filter. It is the most important function in DAX.

What is the difference between a calculated column and a measure?

Show answer

A calculated column is computed row by row at refresh and stored in the model, so it uses memory and can be used in slicers, filters and relationships, for example Profit = Sales[Amount] - Sales[Cost]. A measure is computed at query time in the current filter context and is not stored, for example Total Profit = SUM(Sales[Profit]). Prefer measures for aggregations and use columns only when you need to slice by the value.

What is conditional formatting and where can you apply it?

Show answer

Conditional formatting changes colours, icons, data bars or font based on values or rules, helping users spot good and bad performance quickly. You can apply it to table and matrix cells, backgrounds, font colour, chart data colours and titles using field values, rules or a measure that returns a colour code. A measure returning hex colours gives full control, such as red when below target.

How do you learn a difficult concept like filter context?

Show answer

I learn by building tiny examples. For filter context I made a small model with five products and wrote measures with and without ALL, KEEPFILTERS and ALLSELECTED, checking each result in a table visual. I also read documentation and use DAX Studio to see queries. Explaining it to a teammate afterwards showed me which parts I had not fully understood.

What are tooltips and report page tooltips?

Visualisation and Report DesignMedium

Show answer

Default tooltips show field values when hovering over a data point. Report page tooltips let you design a small custom page, set as a tooltip page type, showing extra visuals like a mini trend chart for the hovered category. They add detail without cluttering the main page. Keep tooltip pages light because they run queries on every hover.

Tell me about a time you had to deliver a report with unclear requirements.

Power BI FundamentalsMedium

Show answer

Situation: an operations head asked for a 'delivery performance dashboard' with no detail. Task: deliver something useful in a week. Action: I held a 30-minute session to list five questions they wanted answered, sketched a page layout and built a quick prototype in two days with sample data. After feedback I finalised it. Result: the dashboard went live on time and became part of the weekly operations review.

How do you handle a business user who keeps changing report requirements?

Show answer

I first understand the goal behind the changes, because frequent changes often mean the core question was not clear. I write down agreed requirements and a mock-up, then deliver in small iterations so feedback comes early. In one project weekly 20-minute reviews cut rework a lot, and changes that were large were logged for the next version rather than added mid-sprint.

What is cross-filter direction and why is bi-directional filtering risky?

Data ModellingMedium

Show answer

Cross-filter direction controls which way filters travel across a relationship. Single direction, from the one side to the many side, is the default and safest. Bi-directional lets the fact table filter the dimension too, which can be useful for slicers showing only relevant values, but it can cause ambiguous paths, unexpected results and slower queries. Prefer single direction and use CROSSFILTER inside specific measures when needed.

What is the difference between COUNT, COUNTA, COUNTROWS and DISTINCTCOUNT?