Introduction Power BI is a business intelligence tool that enables the conversion of raw data into reports and dashboards. However, the quality of the Power BI report is influenced by how the data is prepared and structured in advance. Data modelling is the act of organizing the tables, columns, keys, and relationships in such a way that allows Power BI to process how the various data parts are connected. A correctly constructed model brings better performance of the report, eases calculations in DAX, minimizes redundancy of data, improves scalability, and simplifies understanding of the solution. The article describes key modelling schemes, fact and dimension tables, relationships, direction of filters, and joins in Power Query. It also illustrates the difference between the functions of merging tables in Power Query and creating relationships in the Power BI model. 1. Data Modelling in Power BI What is Data Modelling? Data modelling in Power BI involves designing the structure of tables and defining how they relate to one another. Instead of treating every dataset as one large table, related information can be separated into logical tables and connected using keys. For example, a sales system may contain: Customer information Product information Date information Location information Sales transactions These can be organized into a model where a central FactSales table connects to several descriptive dimension tables. A good data model: Improves report and query performance Makes DAX measures easier to write Reduces unnecessary data duplication Makes relationships and filter propagation easier to understand Allows the model to scale as more data is added Makes reports easier to maintain Reduces the risk of incorrect calculations 1.1 Flat Table A flat table stores all information in one table. All data is kept in one single grid or file without links to other tables; it lacks relational database connections, which often leads to repeated or redundant information. Example Sale_ID Date Customer Product Category City Quantity Sales 1001 01/09/2026 John Laptop Electronics Nairobi 1 80,000 1002 02/09/2026 Mary Mouse Electronics Mombasa 2 3,000 1003 03/09/2026 John Keyboard Electronics Nairobi 1 4,000 Structure FLAT TABLE ┌───────────────────────────────────────────┐ │ Sales │ ├───────────────────────────────────────────┤ │ Sale_ID │ │ Date │ │ Customer │ │ Product │ │ Category │ │ City │ │ Quantity │ │ Sales │ └───────────────────────────────────────────┘ Advantages Simple to understand Easy to import Convenient for small datasets Requires fewer relationships Suitable for simple analysis Disadvantages Creates significant data duplication Can increase model size Changes to customer or product information may need to be repeated across many rows Can make data maintenance difficult Less suitable for large datasets Appropriate Use A flat table can be appropriate for a small, simple dataset where the data is already clean and there are relatively few repeated descriptive values. For large business intelligence solutions, however, a flat table is generally less desirable than a properly designed dimensional model. 1.2 Star Schema A star schema consists of a central fact table surrounded by dimension tables. Structure ┌───────────────┐ │ DimCustomer │ │ CustomerID PK │ └───────┬───────┘ │ 1 │ │ * ┌───────────────┐ ┌────▼───────┐ ┌───────────────┐ │ DimDate │───────►│ FactSales │◄───────│ DimProduct │ │ DateKey PK │ 1:* │ SaleID │ 1:* │ ProductID PK │ └───────────────┘ │ CustomerID │ │ ProductName │ │ ProductID │ │ Category │ │ DateKey │ └───────────────┘ │ Quantity │ │ Sales │ └─────▲──────┘ │ │ * ┌───────┴───────┐ │ DimLocation │ │ LocationID PK │ │ City │ │ Region │ └───────────────┘ The fact table sits at the center, while dimensions provide descriptive information; the fact table has columns that link it to the other tables. Advantages Excellent for Power BI reporting Simple and intuitive structure Generally provides good query performance Makes DAX easier to understand Minimizes unnecessary duplication Makes filtering predictable Easy to extend with additional dimensions Disadvantages Requires careful modelling Dimension tables may contain some repeated information within their own hierarchy Requires appropriate primary and foreign keys Appropriate Use The star schema is particularly appropriate for: Sales reporting Financial reporting Inventory analysis Customer analytics Production reporting Operational dashboards It is generally the preferred structure for Power BI semantic models, sitting between raw database tables and end-user tools, and turning complex schemas and SQL into everyday concepts like "Revenue" or "Customer." 1.3 Snowflake Schema A snowflake schema extends the star schema by dividing dimensions into related tables. For example, instead of storing Product, Category, and Department in one DimProduct table, you can separate them. Structure ┌───────────────┐ │ DimCategory │ │ CategoryID PK │ │ CategoryName │ └───────▲───────┘ │ * │ 1 │ ┌───────┴───────┐ │ DimProduct │ │ ProductID PK │ │ CategoryID FK │ │ ProductName │ └───────┬───────┘ │ * │ 1 │ ┌───────▼───────┐ │ FactSales │ │ ProductID FK │ │ CustomerID FK │ │ SalesAmount │ └───────────────┘ Advantages Reduces duplication within dimensions Useful for complex hierarchical data Can represent normalized source systems more closely Useful where dimensions are very large or have complex structures Disadvantages More tables and relationships More complex filter paths DAX and report development can become less intuitive May require additional joins during analysis More difficult for beginners to understand Appropriate Use Snowflake schemas can be appropriate where dimensions have complex hierarchies or substantial repeated data. However, unnecessarily snowflaking a Power BI model can add complexity without providing enough benefit. Schema Comparison Feature Flat Table Star Schema Snowflake Schema Number of tables One Multiple Multiple Simplicity Very high High Lower Data duplication High Low Very low Relationships Minimal Simple More complex DAX simplicity Moderate High Lower Scalability Limited Excellent Excellent Power BI suitability Small models Excellent Selective Maintenance Difficult as data grows Easy More complex 2. Fact Tables and Dimension Tables A dimensional model normally consists of fact tables and dimension tables. Fact Table A fact table records measurable business events. For example, FactSales may contain: Sale_ID Date_Key Customer_ID Product_ID Location_ID Quantity Sales_Amount Cost_Amount Profit_Amount Fact tables normally contain foreign keys linking them to dimensions and numeric values that can be aggregated. Examples include: FactSales FactOrders FactTransactions FactInventory FactProduction Dimension Table A dimension table contains descriptive information used to analyze facts. Descriptive Attributes: Stores qualitative data like names, categories, and locations (answering who, what, where, when) Primary Key: Uses a unique identifier for each row to link with foreign keys in a fact table Denormalized Structure: Built wide and flat with fewer rows than fact tables to speed up read performance Examples include: DimCustomer CustomerID CustomerName CustomerType Gender City DimProduct ProductID ProductName Category Brand UnitPrice DimDate DateKey Date Month Quarter Year DimLocation LocationID City County Region Country Facts answer questions such as: How many? How much? How often? Grain or Granularity The grain of a fact table describes exactly what one row represents. For example, one row in FactSales might represent one product sold in one sales transaction. This distinction is extremely important: If one row represents an individual product transaction, the table might have: SaleID | ProductID | CustomerID | Quantity | SalesAmount. If one row represents an entire customer order, the grain is different. A clearly defined grain prevents incorrect aggregation and double-counting. Practical Star Schema Example A retail company could use: DimCustomer │ │ 1:* ▼ DimDate ───────► FactSales ◄────── DimProduct ▲ │ │ 1:* │ DimLocation The central FactSales table stores transactions, while the dimensions provide context. For example, selecting Category = Electronics from DimProduct filters FactSales and allows the report to calculate total electronics sales. 3. Relationships in Power BI A relationship defines how two tables are connected through common columns. For example: DimCustomer.CustomerID │ │ 1:* ▼ FactSales.CustomerID CustomerID is unique in DimCustomer but can occur many times in FactSales. This allows Power BI to determine which sales belong to which customer; it acts like a link between the tables. 3.1 One-to-Many Relationship (1:*) This is the most common relationship in a Power BI star schema. One record in the dimension can correspond to many records in the fact table. Example DimCustomer FactSales CustomerID 1 ─────────── * CustomerID 1001 1001 1002 1001 1003 1002 Customer 1001 can have many sales transactions. When to Use Use 1:* when: The dimension contains unique values The fact table contains repeated foreign-key values This should normally be the default relationship in a star schema. In short: a single record in one table (the parent) can connect to multiple records in another table (the child), but each child record links back to only one parent. 3.2 One-to-One Relationship (1:1) A one-to-one relationship means each record in one table corresponds to exactly one record in another table. It occurs when one record in a database table or entity connects to exactly one record in another table, and vice versa. Example EmployeeDetails EmployeeSecurity EmployeeID 1 ─────────── 1 EmployeeID When to Use It may be useful when information about the same entity has been intentionally split into two tables. However, a 1:1 relationship should not be used simply because tables can be joined — in many cases, combining the information into a single dimension is simpler. 3.3 Many-to-Many Relationship (:) A many-to-many relationship occurs when multiple records in one table can relate to multiple records in another. Example Students * ───────── * Courses A student can take many courses, and a course can have many students. In dimensional modelling, many-to-many relationships should generally be handled carefully. A bridge table is often preferable: DimStudent BridgeStudentCourse DimCourse 1 * * 1 │──────────────────┘ └──────────────────│ The bridge table resolves the many-to-many association. In a standard star schema, a single row in a fact table links to only one row in a dimension table (a many-to-one relationship). However, real-world business processes often break this rule. A bridge table sits between the tables, containing pairs of foreign keys or group keys that map multiple dimension items to a single event or group. Primary Keys and Foreign Keys A primary key uniquely identifies a record. For example: DimCustomer CustomerID 1001 1002 1003 CustomerID must be unique. A foreign key references the primary key of another table — a field in one table that points to the unique ID (primary key) in another table to connect them. How It Works The Link: It builds a bridge between two lists of data so the database knows they belong together. The Rule: It acts like a security guard, stopping you from adding data to a table if it does not match a real ID in the connected table. FactSales CustomerID 1001 1001 1002 1003 1001 The same CustomerID can appear many times because a customer can make many purchases. Referential Integrity Referential integrity means that foreign-key values should correspond to valid records in the related dimension. For example, if FactSales contains CustomerID 1005 but DimCustomer does not contain CustomerID 1005, the model has a referential integrity problem. Active and Inactive Relationships An active relationship is normally used automatically when Power BI propagates filters between tables. An inactive relationship exists but is not used automatically. For example, a FactSales table might contain both OrderDate and DeliveryDate. DimDate could have relationships to both columns, but normally only one relationship is active. DAX can explicitly use the inactive relationship when required, for example with USERELATIONSHIP(). 4. Filter Direction Filter direction determines how filters travel between related tables. Single-Direction Filtering In a typical star schema, filtering moves from the dimension to the fact. DimProduct │ │ Filter ▼ FactSales Suppose a report user selects Product Category = Electronics. The filter travels from DimProduct to FactSales, so only sales associated with Electronics are included in the calculation. This is generally the preferred approach because it is predictable and reduces ambiguity; thus requires defining clear objectives, communicating openly, and documenting specific expectations. Bidirectional Filtering Bidirectional filtering allows filters to travel in both directions. DimProduct ↕ FactSales Although this can be useful in specific scenarios, it should not be enabled unnecessarily. Potential problems include: Ambiguous filter paths Unexpected results Circular filtering paths More complicated DAX behavior Increased model complexity Therefore, single-direction filtering should normally be preferred in a standard star schema, while bidirectional filtering should be used only when there is a clear modelling reason. 5. Joins in Power Query Power Query is the data preparation and transformation component of Power BI. A join, performed using Merge Queries, combines information from two tables based on matching columns. For example: Customers CustomerID CustomerName 1 John 2 Mary 3 Peter Orders OrderID CustomerID Amount 101 1 10,000 102 1 5,000 103 2 8,000 104 4 3,000 CustomerID is the matching column. 5.1 Left Outer Join A Left Outer Join retains all records from the left table and matching records from the right table. Customers LEFT JOIN Orders CustomerID CustomerName OrderID Amount 1 John 101 10,000 1 John 102 5,000 2 Mary 103 8,000 3 Peter null null Customer 3 is retained even though there is no order. Use Case: Useful when you want all customers and any orders associated with them. 5.2 Right Outer Join A Right Outer Join retains all records from the right table and matching records from the left table. Customers RIGHT JOIN Orders CustomerID CustomerName OrderID Amount 1 John 101 10,000 1 John 102 5,000 2 Mary 103 8,000 4 null 104 3,000 Order 104 is retained even though CustomerID 4 does not exist in Customers. 5.3 Full Outer Join A Full Outer Join retains all records from both tables. Customers FULL JOIN Orders CustomerID CustomerName OrderID Amount 1 John 101 10,000 1 John 102 5,000 2 Mary 103 8,000 3 Peter null null 4 null 104 3,000 This is useful when the objective is to identify both matched and unmatched records. 5.4 Inner Join An Inner Join retains only records that have matching values in both tables. CustomerID CustomerName OrderID Amount 1 John 101 10,000 1 John 102 5,000 2 Mary 103 8,000 Customer 3 and Order 104 are excluded because they do not have matches on both sides. Use Case: Useful when only matching records are required. 5.5 Left Anti Join A Left Anti Join returns records from the left table that have no matching record in the right table. Customers LEFT ANTI JOIN Orders CustomerID CustomerName 3 Peter This can be used to identify customers who have never placed an order. 5.6 Right Anti Join A Right Anti Join returns records from the right table that have no matching record in the left table. OrderID CustomerID Amount 104 4 3,000 This identifies orders containing CustomerIDs that do not exist in the Customers table. Join Summary Join Type Records Retained Left Outer All left + matching right Right Outer All right + matching left Full Outer All records from both Inner Only matching records Left Anti Unmatched left records Right Anti Unmatched right records 6. Power Query Joins vs Power BI Relationships Although both concepts connect tables, they perform different functions. Power Query Merge A Power Query merge occurs during the data preparation stage. It combines columns from one query with another query based on matching values. Customers + Orders ↓ Merged Table The result can contain: CustomerID, CustomerName, OrderID, OrderDate, Amount. A merge can therefore physically create a wider table containing columns from both sources. Power BI Relationship A relationship is created in the data model after data loading/transformation. It does not physically combine the tables. DimCustomer │ │ 1:* ▼ FactSales The two tables remain separate. Power BI uses the relationship to propagate filters and evaluate calculations. Key Differences Power Query Merge Power BI Relationship Data preparation stage Data modelling stage Combines columns Connects tables logically Creates a resulting query/table Tables remain separate Used for transformation Used for analysis Can increase table width Preserves dimensional structure Similar to SQL JOIN Similar to a logical model relationship When should we merge like real ? A merge may be appropriate when: A column is genuinely required in the resulting table The data is naturally one table after transformation You need to clean or enrich a dataset before loading it Combining the tables reduces unnecessary complexity However, excessive merging can produce extremely wide tables with duplicated descriptive data. For example, repeatedly merging customer, product, location, and other descriptive information into FactSales can effectively recreate a flat table. Why Keep Fact and Dimension Tables Separate? Keeping them separate is normally preferable because: It reduces redundancy It makes the model easier to understand It improves dimensional analysis It supports reusable dimensions It simplifies DAX It allows dimensions to filter multiple fact tables It makes the model easier to maintain and expand For example: DimCustomer │ ▼ DimDate ────────► FactSales ◄──────── DimProduct ▲ │ │ DimLocation The same DimDate could also be used to analyze FactOrders, FactReturns, and FactInventory. 7. Recommended Power BI Model For a typical business intelligence project, a star schema is recommended: DimCustomer │ │ 1:* ▼ FactSales ▲ │ 1:* ┌────────────────────┼────────────────────┐ │ │ │ │ │ │ DimProduct DimDate DimLocation │ │ │ └──────────── 1:* ───┴──── 1:* ──────────┘ More precisely, each dimension should normally have a one-to-many relationship to the fact table: DimCustomer 1 ───────── * FactSales DimProduct 1 ───────── * FactSales DimDate 1 ───────── * FactSales DimLocation 1 ───────── * FactSales Recommended Design Principles Use a star schema provides a good balance between performance, simplicity, and scalability. 2.Keep dimensions separate from facts reduces redundancy and makes the model easier to maintain. Use 1:*\ relationships dimension tables should normally contain unique keys, while fact tables contain repeated foreign keys. Prefer single-direction filtering filters should normally flow Dimension then Fact, reducing ambiguous filter paths and making report behaviour easier to predict. Define the grain of every fact table before building measures, determine exactly what one row represents. Use meaningful primary and foreign keys like DimProduct.ProductID and FactSales.ProductID. Use bidirectional relationships carefully should only be introduced where the business requirement genuinely requires them. Avoid unnecessary snowflaking snowflake design can be useful for complex dimensions, but a simpler star schema is generally easier to work with in Power BI. Use Power Query for transformation cleaning, filtering, splitting, merging, and shaping data should generally happen before the data reaches the semantic model. Use relationships for analysis once tables are appropriately prepared, relationships should connect the fact and dimension tables without unnecessarily physically merging them. Conclusion The key to effective Power BI reporting is having a well-constructed data model. Data modelling defines how Power BI interprets the data of the business, affecting various aspects of reporting such as performance, DAX calculations, filtering, and maintenance of models. A flat table is easy to use, minimizing overhead costs when working with smaller datasets. However, as the data grows, dealing with flat tables can become problematic. A snowflake schema provides excellent normalization but adds additional tables and complexity in building relationships. In business intelligence applications, a star schema is considered the most efficient approach, balancing simplicity and performance. Fact tables need to have a defined level of detail, while dimension tables should have the descriptive characteristics that allow understanding of the facts. Dimensions should have one-to-many relationships with facts, which have unique primary keys and repeating foreign keys. The correct interpretation of relationship connectors is important to provide proper filter propagation, meaning that single relationships from dimensions to facts should generally be applied. Bidirectional relationships should be used only when necessary, as they can create confusion. Finally, it is important to differentiate between the concepts of Power Query merging and Power BI relationships. For a typical business intelligence project, the recommended architecture is therefore: DIMENSIONS │ │ 1:* ▼ FACT TABLE │ │ DAX MEASURES │ ▼ POWER BI REPORT This approach provides a model that is readable, scalable, efficient, and easier to maintain, while allowing users to analyze business performance from multiple perspectives.