"Power Query in Action: Building a Data Model for Power BI Reports"

Introduction to Data Modeling

What is a Data Model?

A data model is the foundation of your Power BI report. It defines how data tables are connected and how they interact with each other. Think of it like a map that tells Power BI how to find and relate information from different tables.

In simpler terms:

• Imagine you have Sales data in one table and Customer details in another.

• A data model links these tables (for example, using Customer ID) so you can analyze sales by customer, location, or time.

Why Is Data Modeling Important in Power BI?

Power BI works best when your data is structured properly. Here is why a solid data model matter:

Makes Analysis Easier

• You can drag and drop fields from different tables into visuals without manually joining them.

• Example: Compare sales by region using data from separate tables.

Reduces Data Errors

• A well-built model avoids duplicates, incorrect totals, or mismatched values.

• Keeps your reports accurate and consistent.

Improves Performance

• A clean model loads faster and responds quicker when filtering or slicing data.

Enables DAX Calculations

• Measures like Total Sales, Profit Margin, or Year-to-Date Sales work only if relationships are clear.

Relationships Between Tables

In Power BI, relationships define how two tables are connected so they can work together in visuals. These connections are made using keys:

• Primary Key → A column in one table with unique values (e.g., CustomerID in Dim_Customer).

• Foreign Key → A column in another table that refers to the primary key (e.g., CustomerID in Fact_Sales).

One-to-Many Relationship (Most common in Star Schema)

• Means one record in the primary key table can match many records in the related table.

• Example:

• One customer in Dim_Customer can have many sales in Fact_Sales.

• Dim_Customer[CustomerID] → Fact_Sales[CustomerID]

Many-to-One Relationship (Just the reverse view)

• This is essentially the same relationship but looked at from the other direction in Power BI.

• Power BI automatically sets the direction depending on your data model.

Types of Data Modeling in Power BI

Star Schema (Recommended)

A Star Schema is a type of data model with:

• One central fact table (that holds measurable data like sales, quantity, revenue).

• Multiple dimension tables (that provide descriptive information like customer names, product categories, or dates).

It is called a star because the diagram looks like a star — the fact table is at the center, and dimension tables are like points radiating out.

here are the sample tables details that I am working on

• Fact Table: Fact_Sales – contains metrics like Quantity and TotalAmount, linked to dimension IDs.

• Dimension Tables:

• Dim_Date – for time-based analysis.

• Dim_Customer – holds customer details.

• Dim_Product – product names and categories.

• Dim_Region – regional data.

Load All Tables into Power BI

1. Open Power BI Desktop.

2. Go to Home → Get Data → Excel.

3. Select your Excel file(s) that contain:

   • Fact_Sales

   • Dim_Date

   • Dim_Customer

   • Dim_Product

   • Dim_Region

4. In the Navigator window, tick all sheets/tables you want, then click Load.

Go to Model View

1. Once the data is loaded, look at the left sidebar in Power BI Desktop.

2. Click the Model icon (it looks like three tables connected by lines).

3. You will see all your tables as separate boxes.

Create Relationships

Now you connect the Fact Table to Dimension Tables.

In Model View:

• Drag DateID from Fact_Sales to DateID in Dim_Date.

• Drag CustomerID from Fact_Sales to CustomerID in Dim_Customer.

• Drag ProductID from Fact_Sales to ProductID in Dim_Product.

• Drag RegionID from Fact_Sales to RegionID in Dim_Region.

Relationship Type:

• These should be One-to-Many (1:*) with the dimension table as "One" side and fact table as "Many" side.

• Make sure Cross Filter Direction is set to Single for performance.

Create Visuals in Power BI

Bar Chart – TotalAmount by RegionName

1. Go to Report View (first icon in the left bar).

2. From Visualizations pane, click Clustered Bar Chart.

3. Drag RegionName from Dim_Region into the Axis

4. Drag TotalAmount from Fact_Sales into the Values

5. The chart will show sales by each region.

Pie Chart – Quantity by ProductName

1. In the Visualizations pane, click Pie Chart.

2. Drag ProductName from Dim_Product to the Legend

3. Drag Quantity from Fact_Sales to the Values

4. This shows which products are selling most in terms of quantity.

Line Chart – TotalAmount over OrderDate

1. Select Line Chart from Visualizations.

2. Drag OrderDate from Dim_Date into the Axis.

3. Drag TotalAmount from Fact_Sales into Values.

4. The line will show sales trend over time.

5. (Tip: Right-click the axis → Sort by → OrderDate to keep it chronological.)

Why This Order Works

• We first model the data so visuals pull the right relationships.

• Then we build visuals using dimension fields for categories (like RegionName, ProductName, OrderDate) and fact fields for measures (like TotalAmount, Quantity).

Snowflake schema:

A Snowflake Schema is a type of database/data model design where your dimension tables are normalized — meaning they are broken down into smaller related tables instead of keeping all details in one table.

Implementing Snowflake Schema in Power BI

Load All Tables

1. Open Power BI Desktop.

2. Go to Home → Get Data → Excel.

3. Load these files one by one:

   • Fact_Sales.xlsx

   • Dim_Customer.xlsx

   • Dim_City.xlsx

   • Dim_Country.xlsx

   • Dim_Product.xlsx

   • Dim_Category.xlsx

4. Click Load.

Switch to Model View

• Click the Model icon on the left sidebar (it looks like a diagram).

• You will see all tables without relationships.

Create Relationships

The Snowflake schema works by linking Fact Table → Dimension Table → Sub-Dimension Table.

Customers & Geography

• Fact_Sales[CustomerID] → Dim_Customer[CustomerID] (One-to-many)

• Dim_Customer[CityID] → Dim_City[CityID] (One-to-many)

• Dim_City[CountryID] → Dim_Country[CountryID] (One-to-many)

Products & Categories

• Fact_Sales[ProductID] → Dim_Product[ProductID] (One-to-many)

• Dim_Product[CategoryID] → Dim_Category[CategoryID] (One-to-many)

Verify Relationship Cardinality

• Ensure Fact → Dimension is Many-to-One ( to 1).

• The 1 side should always be on the dimension table.

Create Visuals

Now that the schema is linked:

Example Visual 1 – Sales by Country

1. Add a Map or Bar Chart.

2. Axis: Dim_Country[CountryName].

3. Values: SUM(Fact_Sales[TotalAmount]).

Example Visual 2 – Sales by Category

1. Add a Pie Chart.

2. Legend: Dim_Category[CategoryName].

3. Values: SUM(Fact_Sales[TotalAmount]).

Example Visual 3 – Quantity by Product

1. Add a Bar Chart.

2. Axis: Dim_Product[ProductName].

3. Values: SUM(Fact_Sales[Quantity]).

Why This Schema Works

• Easier updates: Category changes don’t affect all product records.

• Efficient storage: Data is normalized, avoiding repeated values.

• Flexibility: You can drill down from Country → City → Customer.

Managing and Optimizing the Model

Hide unnecessary columns

Why: Keep the Fields pane clean for report building, prevent mistakes, and speed up the model a bit.

What to hide (typical):

• Technical keys: Fact_Sales[DateID], CustomerID, ProductID, RegionID

• Surrogate keys in dimensions: Dim_Date[DateID], Dim_Customer[CustomerID], etc.

• Helper/sort columns: Dim_Date[MonthNumber], Dim_Date[DayOfWeekNumber]

How (two clicks):

1. Go to Model view → select the column.

2. Right-click → Hide in report view.

(Relationships still work; the column just will not clutter the report builder.)

Naming conventions

Goal: Make fields self-explanatory to report users.

Tables

• Use friendly names: Sales, Customers, Products, Regions, Dates.

• (Behind the scenes they’re fact/dim, but your users don’t need the “Dim_ / Fact_” prefix.)

Columns

• Be specific and consistent:

  • Total Amount (not Amount)

  • Order Date (not Date)

  • Customer Name (not Name)

• Use spaces and proper case (Power BI handles it fine).

Measures

• Keep measures in a dedicated Measures table (an empty table created via Enter data), or use Display Folders.

• Clear names + units + format:

• Total Sales (Currency), Total Quantity (Whole number), Avg Unit Price (Decimal).

Data types & defaults

• Set Data type correctly (Date, Whole number, Decimal, Text).

• For ID columns set Summarization = Do not summarize (Model view → Column tools).

Sort by columns (fix alphabetical sorting)

Problem: Month Name sorts alphabetically (April before February).

Fix: Create a numeric sort helper and tie it in.

Example with your Dates table:

1. In Model view: select Month Name → Column tools → Sort by column → Month Number.

Do the same for Day Name → sort by DayOfWeek Number.

Other useful sorts:

• Product Category → sort by a custom Category Sort Order.

• Region Name → sort by Region Sort Order if you want custom geography order.

Best Practices

Normalization vs Denormalization

Normalization (Snowflake)

• Split dimensions into multiple related tables (e.g., Product → Category).

• Pros: less duplication, easier maintenance.

• Cons in Power BI: more joins → slower; more complex for users.

Denormalization (Star, recommended for Power BI)

• Flatten dimension attributes into one dimension table each (e.g., Products includes Product Name, Category, Brand in the same table).

• Pros: simpler model, faster queries, easier DAX, better UX.

• Use this unless you have huge, shared reference tables that truly need normalization.

Rule of thumb:

Star schema with wide dimensions is almost always best for Power BI performance and usability.

Performance tips (that move the needle)

Model size & columns

• Remove columns you do not use (in Power Query).

• Avoid high-cardinality text columns in the model (e.g., long comments).

• Use correct data types; prefer Whole Number for keys and counts.

Relationships & filters

• Prefer single-direction relationships; avoid unnecessary bi-directional filters.

• Keep a single active relationship between tables (use USERELATIONSHIP() in DAX when needed).

Measures vs calculated columns

• Do aggregations as measures where possible (calculated columns increase model size).

• Pre-compute heavy transformations in Power Query (folded to the source when possible).

Query Folding

• In Power Query, do filters/joins/types early and keep steps foldable (when source is SQL/Parquet/etc.).

• Avoid steps that break folding (complex custom functions, row-by-row ops) unless necessary.

Auto Date/Time

• Turn off Auto date/time (Options → Global & Current file → Data Load) and use your Dates table instead.

Disable load for staging queries

• If you create staging/base queries in Power Query, right-click query → Enable load (off) to avoid clutter and save memory.

Encoding & formatting

• Set correct format for measures (currency, percent), but avoid over-formatting columns.

• For very large models, consider Aggregation tables (advanced) and Incremental refresh (Premium/PPU).