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 Fact Sales is connected to several descriptive dimension tables.
A good data model is important because it has the following qualities: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.
Top comments (0)