Power Bi Data Preparation & Relationships For Interviews

A income caput opens Monday’s Power BI dashboard and sees gross doubled overnight. The business did not double - a many-to-many narration softly duplicated transactions, and the study became confident, beautiful, and wrong.

Power Query is wherever you connect, clean, reshape, and load information earlier it reaches the study model.
The information model is the semantic layer: tables, columns, relationships, hierarchies, and calculations arranged for analysis.
The safest Power BI creation is usually a star schema: truth tables successful the centre, magnitude tables astir them.
A relationship connects tables done cardinal columns and controls really filters travel crossed the model.
Good models person clear grain, unsocial magnitude keys, debased blank lookup rates, and mostly one-to-many relationships.
The communal interview-winning phrase: “I cleanable successful Power Query, exemplary arsenic a prima schema, validate relationships, past build visuals.”

Think of Power BI arsenic 3 connected decisions: first make the information usable, past make it logically analyzable, past make relationships behave predictably. If the exemplary is weak, moreover the champion ocular is conscionable a polished mistake.

Power BI halfway pipeline The fig shows information moving from sources to Power Query, the model, relationships, and study visuals. Sources Excel, SQL, ERP Power Query Clean Transform Data Model Tables Keys Reports Filters Charts Relationships power select flow Power Query prepares the data, but the exemplary and relationships determine whether the study answers business questions correctly.

Core Explanation: The Three Layers You Must Get Right

Power Query is the ETL-like mentation area of Power BI. ETL intends extract, transform, load: return information from sources, cleanable it, reshape it, and load it into the model.

Typical Power Query tasks see removing duplicates, changing information types, splitting columns, merging tables, appending rows, unpivoting monthly columns into a azygous period column, and creating reusable translator steps. These steps are recorded successful the Applied Steps pane and written successful the M language down the scenes.

The data model is wherever analytical building begins. Instead of keeping 1 elephantine level sheet, you abstracted business events from business descriptors:

Fact table: stores measurable events, specified arsenic income transactions, invoices, clicks, deliveries, aliases payments.
Dimension table: stores descriptive context, specified arsenic product, customer, city, date, channel, aliases employee.
Grain: the level of item of 1 row, specified arsenic “one statement per invoice line” aliases “one statement per shop per day.”
Key: the file utilized to link tables, specified arsenic ProductID, CustomerID, aliases DateKey.

Power BI modelling ladder A layered ladder showing really earthy information becomes a trusted Power BI report. 1. Raw sources 2. Clean successful Power Query 3. Define atom and keys 4. Build relationships 5. Trusted reports Weak little layer breaks the top A Power BI study is only arsenic reliable arsenic the modelling furniture beneath it.

Power Query: What to Do Before Loading Data

In interviews, do not picture Power Query arsenic “just cleaning.” It is the spot wherever you make information model-ready. The output should beryllium narrow, typed, consistent, and aligned to the atom of analysis.

Imagine Titan Company analysing watch income crossed stores, online channels, SKUs, cities, and promotions. A anemic exemplary would support 1 immense income extract pinch product, store, customer, and day fields repeated connected each row. A stronger exemplary would support income transactions arsenic the truth array and link them to Product, Store, Date, and Channel dimensions - reducing repetition and making filters predictable. The strategical truthful what: modelling clarity lets managers comparison brand, region, and transmission capacity without accidentally double-counting sales.

The Data Model: Star Schema Beats the Flat File

The classical Power BI creation is the star schema. One aliases much truth tables beryllium successful the centre, and magnitude tables situation them. The style matters because filters usually travel from dimensions to facts: prime “Mumbai” successful the Store dimension, and the Sales truth array gets filtered to Mumbai sales.

Star schema successful Power BI A truth income array successful the centre connected to date, product, store, customer, and transmission dimensions. Fact Sales Qty, Revenue Dim Date Dim Product Dim Store Dim Customer Dim Channel Dimensions select facts; facts shop measurable events. A prima schema separates descriptive discourse from measurable events, making reporting faster and safer.

Use a snowflake schema only erstwhile magnitude tables request to beryllium normalized further, specified arsenic Product connected to Category connected to Division. In astir MBA question and reply answers, say: “I for illustration a prima schema for simplicity and capacity unless location is simply a clear logic to snowflake.”

Relationships: The Hidden Logic Behind Every Slicer

A relationship connects 2 tables utilizing columns that correspond the aforesaid business key. In Power BI, the important narration choices are cardinality, select direction, and whether the narration is active.

Good and risky Power BI relationships A comparison of a safe one-to-many narration and a risky many-to-many relationship. Usually safe Dim Product Fact Sales 1 : * One merchandise filters galore income rows Risky without design Customer Segments Campaign Leads * : * Use bridge A span array prevents accidental duplication. One-to-many pinch single-direction filtering is the default safe pattern; many-to-many needs definitive design.

Model Quality Checks and Metrics

Do not opportunity “I will cheque if the exemplary is correct” and extremity there. Name the checks. A beardown expert validates keys, statement counts, refresh behavior, and select results earlier trusting the dashboard.

Worked Example: Spot the Relationship Problem

Suppose a Sales truth array has 10,000 transaction rows. The Product magnitude has ProductID arsenic the key. During validation, 180 income rows person ProductIDs that do not beryllium successful the Product table.

Fact lookup lucifer complaint = 9,820 / 10,000 = 98.2%.
Blank lookup complaint = 180 / 10,000 = 1.8%.
If the business target is supra 99% lucifer rate, this exemplary should not beryllium released yet.

The hole is not to hide blanks successful the visual. The hole is to trace whether those ProductIDs are caller SKUs, typing errors, missing maestro data, aliases delayed magnitude refresh. This is precisely the benignant of applicable reply interviewers reward.

Definitions You Can Say successful One Breath

Power Query: Microsoft’s information connectivity and translator acquisition for discovering, connecting, combining, and refining information crossed sources.

Data model: A system group of tables, columns, relationships, and calculations utilized to reply analytical questions.

Relationship: A relationship betwixt 2 tables done matching cardinal columns that controls really filters and calculations propagate.

Star schema: A modelling creation pinch cardinal truth tables connected to surrounding magnitude tables.

Grain: The nonstop business meaning of 1 statement successful a table.

Case Study: Heathrow Airport and Operational Visibility

Heathrow utilized Microsoft’s Power Platform ecosystem, including Power BI, to amended visibility crossed analyzable airdrome operations wherever timing, handoffs, and exceptions matter.

A beardown Power BI exemplary turns operational sound into a shared root of truth.

Situation: An airdrome is simply a unrecorded strategy of flights, gates, passengers, baggage, information queues, retail, maintenance, and unit movements. Each usability tin person its ain systems and definitions. Without a accordant model, 2 teams tin reason complete the aforesaid KPI because their information was joined aliases filtered differently.

The move: Heathrow’s broader integer translator pinch Microsoft devices focused connected giving teams amended entree to operational information and dashboards. In Power BI terms, the captious modelling instruction is clear: do not build 1 elephantine airdrome spreadsheet. Separate arena facts specified arsenic formation movements aliases work incidents from dimensions specified arsenic terminal, airline, gate, date, and work type.

Outcome aliases lesson: The superior driver of amended visibility is not the dashboard canvas - it is simply a disciplined operational information model. Supporting drivers see standardized definitions, timely refreshes, usable self-service reports, and governance astir who owns each information field. The takeaway for interviews: Power BI becomes powerful erstwhile business complexity is translated into cleanable 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 wherever AI helps and wherever quality modelling judgement is still essential.

Power BI Copilot and natural-language study creation: Copilot tin thief draught study pages, summarize insights, and propose measures erstwhile enabled successful Microsoft Fabric aliases Power BI environments. But it still depends connected cleanable array names, relationships, and semantic definitions.
AI-assisted information preparation: Power Query features specified arsenic fuzzy matching and AI-assisted transformations tin thief standardize messy names, categories, and matter fields. Use them pinch validation because “close match” tin beryllium vulnerable successful customer, product, aliases vendor maestro data.
Semantic exemplary documentation: LLMs tin thief explicate schemas, observe ambiguous array names, and propose star-schema restructuring, but they cannot corroborate business atom unless you supply business context.

Export aliases picture your Power BI tables, columns, and relationships, past inquire ChatGPT: “Identify truth tables, magnitude tables, narration risks, imaginable many-to-many issues, and 5 validation checks for this model.” Use the consequence arsenic a reappraisal checklist - not arsenic unsighted truth.

Interview Relevance

“You are fixed income information successful Excel, merchandise maestro data, shop maestro data, and regular targets. How would you hole and exemplary this successful Power BI earlier building the dashboard?”

Use this condemnation erstwhile stuck: “Before designing visuals, I would first make the exemplary trustworthy - cleanable transformations, defined grain, prima schema, valid keys, and tested relationships.”

Common Mistake

The biggest correction is treating Power BI for illustration Excel pinch prettier charts - loading 1 level file, creating visuals immediately, and ignoring atom and relationships. It costs candidates because it shows they tin make dashboards but not trusted analytics. One-line fix: ever explicate the travel from Power Query cleanup to star-schema exemplary to validated relationships earlier talking astir visuals!