Power BI Data Preparation & Relationships for Interviews

A sales head opens Monday’s Power BI dashboard and sees revenue doubled overnight. The business did not double - a many-to-many relationship quietly duplicated transactions, and the report became confident, beautiful, and wrong.

Think of Power BI as three connected decisions: first make the data usable, then make it logically analyzable, then make relationships behave predictably. If the model is weak, even the best visual is just a polished mistake.

Core Explanation: The Three Layers You Must Get Right

Power Query is the ETL-like preparation area of Power BI. ETL means extract, transform, load: take data from sources, clean it, reshape it, and load it into the model.

Typical Power Query tasks include removing duplicates, changing data types, splitting columns, merging tables, appending rows, unpivoting monthly columns into a single month column, and creating reusable transformation steps. These steps are recorded in the Applied Steps pane and written in the M language behind the scenes.

The data model is where analytical structure begins. Instead of keeping one giant flat sheet, you separate business events from business descriptors:

• Fact table: stores measurable events, such as sales transactions, invoices, clicks, deliveries, or payments.
• Dimension table: stores descriptive context, such as product, customer, city, date, channel, or employee.
• Grain: the level of detail of one row, such as “one row per invoice line” or “one row per store per day.”
• Key: the column used to connect tables, such as ProductID, CustomerID, or DateKey.

Power Query: What to Do Before Loading Data

In interviews, do not describe Power Query as “just cleaning.” It is the place where you make data model-ready. The output should be narrow, typed, consistent, and aligned to the grain of analysis.

The Data Model: Star Schema Beats the Flat File

The classic Power BI design is the star schema. One or more fact tables sit in the centre, and dimension tables surround them. The shape matters because filters usually flow from dimensions to facts: select “Mumbai” in the Store dimension, and the Sales fact table gets filtered to Mumbai sales.

Use a snowflake schema only when dimension tables need to be normalized further, such as Product connected to Category connected to Division. In most MBA interview answers, say: “I prefer a star schema for simplicity and performance unless there is a clear reason to snowflake.”

Relationships: The Hidden Logic Behind Every Slicer

A relationship connects two tables using columns that represent the same business key. In Power BI, the important relationship choices are cardinality, filter direction, and whether the relationship is active.

Model Quality Checks and Metrics

Do not say “I will check if the model is correct” and stop there. Name the checks. A strong analyst validates keys, row counts, refresh behavior, and filter results before trusting the dashboard.

Worked Example: Spot the Relationship Problem

Suppose a Sales fact table has 10,000 transaction rows. The Product dimension has ProductID as the key. During validation, 180 sales rows have ProductIDs that do not exist in the Product table.

• Fact lookup match rate = 9,820 / 10,000 = 98.2%.
• Blank lookup rate = 180 / 10,000 = 1.8%.
• If the business target is above 99% match rate, this model should not be released yet.

The fix is not to hide blanks in the visual. The fix is to trace whether those ProductIDs are new SKUs, typing errors, missing master data, or delayed dimension refresh. This is exactly the kind of practical answer interviewers reward.

Definitions You Can Say in One Breath

Case Study: Heathrow Airport and Operational Visibility

Situation: An airport is a live system of flights, gates, passengers, baggage, security queues, retail, maintenance, and staff movements. Each function can have its own systems and definitions. Without a consistent model, two teams can argue over the same KPI because their data was joined or filtered differently.

The move: Heathrow’s broader digital transformation with Microsoft tools focused on giving teams better access to operational data and dashboards. In Power BI terms, the critical modelling lesson is clear: do not build one giant airport spreadsheet. Separate event facts such as flight movements or service incidents from dimensions such as terminal, airline, gate, date, and service type.

Outcome or lesson: The primary driver of better visibility is not the dashboard canvas - it is a disciplined operational data model. Supporting drivers include standardized definitions, timely refreshes, usable self-service reports, and governance around who owns each data field. The takeaway for interviews: Power BI becomes powerful when business complexity is translated into clean facts, dimensions, and relationships.

How AI Changes Power BI: Power Query, the Data Model & Relationships

AI is making Power BI faster, but not magically correct. In 2026, the student advantage is knowing where AI helps and where human modelling judgment is still essential.

• Power BI Copilot and natural-language report creation: Copilot can help draft report pages, summarize insights, and suggest measures when enabled in Microsoft Fabric or Power BI environments. But it still depends on clean table names, relationships, and semantic definitions.
• AI-assisted data preparation: Power Query features such as fuzzy matching and AI-assisted transformations can help standardize messy names, categories, and text fields. Use them with validation because “close match” can be dangerous in customer, product, or vendor master data.
• Semantic model documentation: LLMs can help explain schemas, detect ambiguous table names, and propose star-schema restructuring, but they cannot confirm business grain unless you provide business context.

Interview Relevance

Common Mistake

What to Revise Next

Once Power Query, the data model, and relationships are clear, revise the calculation and delivery layers next. The natural journey is: