Velma Ketra Lukaya

Posted on Oct 2

From Raw Data to Published Reports: A Practical Guide to the Power BI Workflow

#analytics #data #tutorial

Power BI is often described as a visualisation tool, but its real value lies in the workflow that precedes the visuals: connecting to data, assessing and cleaning it, calculating with DAX, structuring it into a well-designed model, and finally publishing and securing the result.

This article follows that workflow in the order in which it is typically learned and applied and recommends model design for a typical business intelligence project.

1. The Power BI Ecosystem

Component	Description	Role in the workflow
Power BI Desktop	Free Windows application for connecting to data (Excel, CSV and others), cleaning it, modelling it, writing DAX, and building visualisations, dashboards and reports	Authoring
Power BI Service	Cloud-based (browser) service for publishing, sharing and collaboration, with real-time updates; the full feature set is a paid licence	Publishing and sharing
Power BI Mobile	On-the-go access to reports and interaction with visuals; not used for report creation	Consumption
Power BI Report Server	Hosts reports on an organisation's own server, typically for data-security reasons	On-premises hosting

2. Introduction: Power BI as a Workflow

Power BI is a Microsoft tool that turns raw data into interactive insight. Compared with Excel, it is designed for

Larger datasets
Supports dashboards, reports and analytical calculations
Work with real-time data. It shares many function names with Excel, but its calculation language is DAX (Data Analysis Expressions).

Treating Power BI purely as a charting tool misses most of its value. Reliable reports depend on a connected sequence of stages:

data  →  preparation  →  modelling  →  analysis  →  visualisation  →  sharing

Each stage depends on the one before it. A data-quality problem that is not corrected in preparation becomes a modelling error, then a calculation error, and finally an incorrect figure on a dashboard. The sections that follow treat the stages in order.

Power BI Desktop, the environment in which data is connected, prepared, modelled and visualized.

Desktop interface

Home: Get data and Transform data.
Modeling: Manage relationships.
Insert: add a visual.
View: control layout.
Fields pane: lists all fields in the model (columns are fields; records are rows).
Visualizations pane: used to select and configure a chart or table.
Three views: Report (create and view reports), Model (manage relationships), Table (data in tabular form).

3. Data Sources

Power BI connects to a range of external sources, including Excel, CSV, SQL databases and the web. In the course, a Kenya crops dataset served as the working example. It passes through Power Query for cleaning and transformation, then into the data model, where relationships are created between tables holding different information.

 Excel ─┐
 SQL   ─┤
 CSV   ─┼──►  Power BI Desktop  ──►  Power Query  ──►  Data model
 Web   ─┘                           (clean and        (relationships)
                                     transform)

The choice of source influences how much preparation is required, which is why inspection (Section 4) follows immediately after import.

4. Importing and Inspecting the Raw Dataset

Importing data is the beginning of the work. Before any analysis, the dataset should be examined in Power Query, which has four main areas:

Ribbon: transformation commands.
Query pane: lists every table; each can be worked on individually.
Data preview: the rows and columns of the selected table.
Applied Steps: a recorded list of every change, which also functions as an undo mechanism.

Common data-quality problems

Numbers stored as text. A numeric-looking column that is left-aligned in the preview is usually being treated as text.
Unrecognised dates.
Missing values and pseudo-blanks: N/A, Error, blank, null.
Numeric columns with the wrong data type.
Duplicate records.

A missing value is not automatically zero

The meaning of a missing value depends on the column. A blank in a pest-control column may be a genuine answer (no pest control used), whereas a blank in a county column is simply absent information. Replacing every blank with 0 converts "unknown" into "zero" and distorts averages and totals later in the analysis.

Identifiers are text, not numbers

Some values that look numeric should be stored as text: ID numbers, employee IDs, phone numbers and coordinates. They are never summed or averaged, and numeric storage can strip leading zeros. Data types should reflect what a column means.

A practical convention for blanks, by column type:

Numeric column: leave as null, or replace with a number.
Text column: use a consistent label such as N/A or Not provided.

5. Cleaning and Transforming Data in Power Query

5.1 Data types and errors

When a column intended to be numeric or date-typed contains text, errors appear because of the mismatch. An error can be handled in several ways:

replace with null/blank;
replace with the actual value, if known;
remove the row;
replace with the median or mean (for example, calculated without outliers).

To correct a data type, select the column and use Transform → Detect data type.

5.2 Consistent categories

Use one name to represent a county.
Use one category to represent blank, unknown and similar values.
Fill missing values with the mode only when filling is unavoidable.

5.3 Duplicates

Duplicates are removed using a unique ID: rows sharing the same unique ID represent the same record.

5.4 Formatting and applying changes

Use Transform columns → Format for text clean-up, then Close & Apply to load the result into the model. Every action is recorded in Applied Steps, so the process is reversible and repeatable.

Why this precedes modelling

Everything downstream trusts this stage. Relationships fail on keys with inconsistent spellings, averages are wrong if blanks were silently converted to zeros, and counts are inflated by duplicates.

6. DAX: From Preparation to Analysis

DAX (Data Analysis Expressions) is the formula language Power BI uses to create calculations: calculated columns, measures and calculated tables. Where Excel formulas operate on cells, DAX operates on columns and tables and responds to filters (the filter context), a concept that recurs throughout the following sections.
DAX functions fall into six categories:

Category	Purpose
Aggregate	Mathematical and statistical summaries
Filter	Modify or create filter context and tables
Logical	Evaluate conditions to support decisions and categorisation
Text	Format and clean text
Date and Time	Extract and compare date components
Time Intelligence	Period-based calculations (see note in Section 7.9)

7. DAX Function Categories

7.1 Aggregate functions

Function	Behaviour
`SUM`	Adds all numbers in a column
`AVERAGE`	Arithmetic mean
`MIN`, `MAX`	Lowest and highest value
`MEDIAN`	Middle value of the data
`COUNT`, `COUNTA`	Count non-blank values
`COUNTROWS`	Counts rows in a table
`DISTINCTCOUNT`	Counts distinct values
`COUNTBLANK`	Counts blanks
Standard deviation, variance	Measures of spread

Mean versus median. The mean is affected by outliers; the median is not. When the mean and median are close, the data is approximately normally distributed. For skewed variables (left- or right-skewed), the median often represents a typical value better.

Iterators: SUMX and AVERAGEX. These evaluate an expression for each row of a table and then sum or average the results. They are used when a quantity is not stored in the table but can be derived from columns that are, for example revenue computed from yield and price. The table reference comes first, followed by the expression. The calculation requires knowing what constitutes the value, not the value itself, and blanks in the inputs will change the result.

Illustrative example

Total Revenue = SUMX( Crops, Crops[Yield] * Crops[Market Price] )

Arithmetic versus geometric mean. AVERAGE is the ordinary arithmetic mean. GEOMEAN multiplies the values and takes the nth root, and is appropriate for ratios, growth rates and compounding changes.

7.2 Arithmetic functions

Arithmetic in DAX is mostly expressed through operators and expressions such as the SUMX example above. DIVIDE is the safe division function and handles division by zero gracefully, which is useful in ratio measures.

7.3 Filter functions

Filter functions ensure that calculations reflect the intended filter context.

FILTER returns a subset of a table that meets a condition. The result is a new table that can be named and used like any other table.
ALL ignores the active filters.
ALLEXCEPT removes all filters except those on a specified column.
A check for whether a column is filtered to a single value (HASONEVALUE) returns TRUE when the column has exactly one value in the current context.
CALCULATE is covered in Section 7.4.

Syntax conventions

FILTER( Table, Column = "value" )          -- e.g. only Kericho county

Illustrative example:

Kericho Only =
FILTER( 'Kenya_Crops_Dataset 5', 'Kenya_Crops_Dataset 5'[County] = "Kericho" )

&& is logical AND (all conditions must be met) and must be written with two ampersands; a single & is concatenation.

|| is logical OR that requires only one of the stated conditions to be met to return results of the DAX function

For several values from one column, use the IN operator with curly brackets, for example {"Nairobi", "Meru", "Nakuru"}.

Excel's conditional aggregations (SUMIF, AVERAGEIF) have no direct DAX functions of the same name; the equivalent is built with CALCULATE.

7.4 CALCULATE

CALCULATE evaluates an expression in a modified filter context. The expression is typically an aggregate (sum, average, min, max). Because it returns a single value, it is used to create a measure. It accepts multiple filter conditions separated by commas; for two conditions on the same column, use IN.

Examples of questions answered with CALCULATE:

total yield for a given county;
market price for Nairobi;
total cost of production for farmers who planted more than 10 acres of maize in Kiambu county;
how many farmers made a loss;
average market price of tomatoes planted during the dry season.

Sales Today = CALCULATE( SUM( Sales[Sales] ), Sales[Date] = TODAY() )

If a total-sales measure already exists, it can be reused:

Sales Today = CALCULATE( [Total Sales], Sales[Date] = TODAY() )

Illustrative example:

Kiambu Maize Cost =
CALCULATE(
    SUM( Crops[Cost of Production] ),
    Crops[County] = "Kiambu",
    Crops[Crop Type] = "Maize",
    Crops[Planted Area] > 10
)

7.5 ALL and removing filters

To prevent a KPI from being affected by slicers or filters, apply ALL to the relevant table or columns. ALLEXCEPT ignores filters on every column except the one named (for example, county).
e.g a measure named Total Revenue all county, which sums Revenue (KES) while applying ALL to the County and Crop Type columns, so the card ignores slicers on those columns.

Total Revenue all county =
CALCULATE(
    SUM( 'Kenya_Crops_Dataset 5'[Revenue (KES)] ),
    ALL( 'Kenya_Crops_Dataset 5'[County] ),
    ALL( 'Kenya_Crops_Dataset 5'[Crop Type] )
)

A measure using ALLEXCEPT so that the card ignores all filters except County and Crop Type filters.

7.6 Logical functions

Logical functions evaluate conditions and return values depending on whether those conditions are true or false. They support decision-making, filtering and categorising data (for example profitable or non-profitable; low, no or high profit).

Function	Behaviour
`IF`	Tests a condition; two conditions, two outputs
Nested `IF`	More than two conditions and outputs
`AND`	True only if both conditions are true
`OR`	True if at least one condition is true
`NOT`	Reverses the logical value
`SWITCH`	A cleaner alternative to nested `IF`; evaluates an expression against matching values; `SWITCH( TRUE(), … )` allows multiple conditions
`ISBLANK`	Tests for blanks, usually inside `IF`

Two points matter in practice. Blanks fall into the "else" branch of an IF, so ISBLANK should be used where blanks need separate handling. And AND/OR used alone return only TRUE/FALSE; nested in an IF, the output can be any quoted text.

Illustrative example:

Profit Category =
IF( ISBLANK( Crops[Profit] ), "Not recorded",
    IF( Crops[Profit] < 0, "Loss", "Profitable" ) )

Use of AND

Use of OR

7.7 Text functions

Text functions clean and format text: LOWER, UPPER, PROPER, CONCATENATE/CONCAT, SPLIT, TRIM, CLEAN, LEFT and RIGHT. Because they are data-preparation tasks, they are generally better applied in Power Query.

TRIM removes extra leading or trailing spaces.
CONCATENATE joins two text values without a delimiter; for more values, or to insert a space or word, use the & operator.

Illustrative example:

Full Label = Crops[County] & " - " & Crops[Crop Type]

Without space

With space

7.8 Date and time functions

Need	Function
Current date / date and time	`TODAY`, `NOW`
Extract components (numeric)	`YEAR`, `MONTH`, `DAY`, `HOUR`, `MINUTE`, `SECOND`
Text form of a component	`FORMAT( date, "YYYY" )`, `"YY"`; `"MMMM"` full month name, `"MMM"` short; `"DDDD"` day name
Quarter	`FORMAT( date, "Q" )` (see appendix)
Difference between two dates	`DATEDIFF( date1, date2, interval )` with interval day, month or year
Working days between dates	`NETWORKDAYS` (excludes weekends, optionally holidays)
Build a date	`DATE`
Week number	`WEEKNUM`; return type 1 = week starts Sunday, 2 = week starts Monday

Illustrative example:

Order Month Name = FORMAT( Orders[Order Date], "MMMM" )
Days to Delivery = DATEDIFF( Orders[Order Date], Orders[Delivery Date], DAY )

7.9 Time intelligence

Time-intelligence functions form a category in the course structure, but no specific functions or examples were covered, so none are presented here as demonstrated content.

8. Understanding DAX Outputs

Before writing a formula, the first question is: what output is required?

Required output	DAX object	Notes
A single value	Measure	Appears in the Fields pane; usually shown in a Card visual
A column in which each row is evaluated separately	Calculated column	Adds an entire column to a table
A new table	Calculated table	For example, data for a specific date or specific counties

A measure is evaluated according to the filter context in which it is used, so it responds to slicers and to the rows and columns of a visual. A calculated column is evaluated row by row when the model is refreshed. Choosing the wrong object type is a common source of errors even when the formula itself is correct.

9. From DAX Functions to Business Questions

DAX is not an exercise in memorising formulas. The essential step is understanding the question and identifying what to filter, what to aggregate and what output is needed.

Business question	Approach
Total sales for today	Filter the sum to that date: `CALCULATE( SUM( sales ), date = TODAY() )`, or `CALCULATE( [Total Sales], date = TODAY() )`
How many sales	Count rows or transactions
How many customers	Counts people who completed transactions, regardless of how many items each bought; `COUNTROWS` or `COUNT` on the customer ID. Different approaches can give the same answer
*How many different* customers**	A distinct count (`DISTINCTCOUNT`)
Total yield or market price for a county	`CALCULATE` with a county filter
Average market price for tomatoes in the dry season	`CALCULATE` with `AVERAGE` and two conditions

These calculations assume tables that are organised sensibly. If the same customer is recorded under several spellings, "how many different customers" cannot be answered correctly. This is the bridge to data modelling.

10. Data Modelling: Structuring Data for Analysis

Data modelling is the process of organizing tables and defining the relationships between them so that data can be correctly filtered, analyzed and summarized. It involves two tasks:

defining and organizing tables;
defining relationships between tables.

Why it matters

Better reporting structure.
Reduced repetition.
Fewer errors.
Ease of creating reports.

A well-designed model also keeps DAX simple, supports query performance (each visual issues a query against the model, and fact/dimension designs suit how those queries filter, group and summarise), improves readability and maintainability, and scales more gracefully as new subject areas are added.

11. Flat Table

A flat table holds all information in one table. The Kenya crops dataset began this way: county, crop type, planted area, revenue and price all sit in each row.

FLAT TABLE   (illustrative diagram)
┌────────────────────────────────────────────────────────────┐
│ County | Crop  | Farmer | Season | Area | Yield | Revenue … │
│ Kiambu | Maize | F001   | Dry    | 12   | …     | …         │
│ Kiambu | Maize | F002   | Dry    | 8    | …     | …         │
│ Kericho| Tea   | F003   | Wet    | 20   | …     | …         │
└────────────────────────────────────────────────────────────┘
   descriptive text is repeated on every row

Aspect	Assessment
Advantages	Simple to understand; quick to start; no relationships to configure; adequate for a small, single-topic dataset
Disadvantages	Descriptive values repeat on every row; corrections must be made in many places; tables become very wide; descriptions and measurements are mixed
When appropriate	Small, one-off analyses with a single subject
Power BI implications	More repeated text is stored; no reusable dimensions to filter from; scales poorly as subjects are added

The multi-table alternative stores facts (such as transactions or hospital visits) separately from detail tables (such as doctor details), linked to the main table to avoid repetition.

12. Fact Tables and Dimension Tables

Fact table

A fact table:

Keeps the specific records, events or transactions to be analyzed (for example, all patients who visit a hospital);
Measures something;
Contains numerical records, metrics and quantities;
Allows the grain of the data to be understood;
Contains both primary and foreign keys.

Grain (granularity) is a description of what each record in a table represents. It indicates what was done. The test for any fact table is: what does one row represent? In the hospital example, one row of FactVisit represents one patient visit. If the grain is unclear, counts and totals become unreliable; for instance, if a visit were repeated once per test performed, "number of visits" would be overstated.

Generic examples : FactSales (one row per sale line), FactOrders (one row per order), FactTransactions (one row per payment).

Dimension table

A dimension table

Describes something, giving context for the events held in the fact table (for example, patient details);
Contains descriptive text content;
Has a primary key.

Example: DimPatient, DimDoctor, DimDepartment, DimCustomer, DimProduct, DimDate, DimLocation.

	Fact table	Dimension table
Answers	What happened? How much?	Who, what, where, when?
Contents	Numbers, metrics, keys	Descriptive attributes
One row represents	One event (the grain)	One entity
Keys	Primary and foreign keys	Primary key
Typical size	Many rows	Fewer rows

Separating them means each description is stored once, while the fact table stays focused on events.

13. Star Schema

A star schema consists of one central fact table surrounded by dimension tables, each connected to the fact table.

                 DimDepartment
                       │
                       │
   (other dimension) ── FactVisit ── DimPatient
                       │
                       │
                   DimDoctor

Advantages

Simple, readable model diagram.
Descriptions are stored once, reducing repetition.
Dimensions filter and the fact table is summarised, which matches the way Power BI visuals query a model.
Simpler DAX: measures aggregate fact columns while slicers come from dimensions.
Easy report creation.

Disadvantages

Requires planning, since a flat table must be split into facts and dimensions.
Some descriptive values still repeat within a dimension (for example, a county name for every patient in that county).

Appropriate use: most business intelligence and reporting models.

14. Snowflake Schema

A snowflake schema works like a star schema, but the dimension tables are broken down into several smaller related tables. The structure is hierarchical: fact → dimension → sub-dimension.

 DimCountry ── DimCounty ── DimPatient ── FactVisit

	Star	Snowflake
Dimensions	One table per dimension	Dimension split into related sub-tables
Redundancy	Some repetition inside dimensions	Less repetition (more normalized)
Relationship hops from slicer to fact	One	Two or more
Model diagram	Compact	Larger, more tables

Advantages:
-Less repeated data within dimensions;
-Explicit hierarchies;
-A shared lookup (such as county) can be maintained in one place.

Disadvantages:
-More tables and relationships,
-Longer filter paths, and a
-Busier model that is harder for report builders to navigate.

Appropriate situations: when a sub-dimension is shared by several dimensions, or when the source data is already normalized.

15. Comparing Flat, Star and Snowflake Schemas

Feature	Flat table	Star schema	Snowflake schema
Structure	Everything in one table	One fact table plus dimension tables	Fact table plus dimensions split into sub-tables
Number of tables	1	Few	Most
Redundancy	High	Low to moderate	Lowest
Model complexity	Simplest to set up; harder to maintain as it grows	Moderate; easy to read	Highest
DAX / reporting	Adequate for simple cases; filtering limited to that one table	Simple: measures on facts, slicers from dimensions	Works, but filters pass through more relationships
Performance	Wide tables with repeated text can be inefficient	Recommended by Microsoft for Power BI	Extra relationship hops; usually no benefit unless the structure is needed
Scalability	Poor	Good	Good, with growing complexity
Maintainability	Corrections repeated across many rows	Fix a description once	Good for hierarchies; heavier to manage
Appropriate use	Small, single-topic analysis	Most BI projects	Shared hierarchies; normalised sources

16. Primary Keys and Foreign Keys

A primary key is a unique identifier column that gives each record in a table a unique identity.
A foreign key is a primary key from one table that is used in a different table.
A common column is required before a relationship can be created.

Example. CustomerID is unique in DimCustomer (one row per customer) but may appear many times in FactSales, because one customer can make many purchases. DimCustomer[CustomerID] is therefore a primary key, FactSales[CustomerID] is a foreign key, and the relationship is one-to-many.

Illustrative example:

DimCustomer

CustomerID (PK)	CustomerName
C1	Amina
C2	Brian

FactSales

SaleID	CustomerID (FK)	Amount
S1	C1	500
S2	C1	300
S3	C2	450

Referential integrity means every foreign-key value in the fact table has a matching primary key in the dimension. If FactSales contained C9 but DimCustomer had no C9, those sales would not attach to any customer and would appear under a blank member in visuals.

17. Relationships in Power BI

A relationship shows how tables are connected. It is created by matching a primary key in a dimension table with a foreign key in the fact table. Relationships are necessary because, once data is distributed across tables, Power BI needs to know which fact rows belong to which dimension rows. They are what allow filters to travel from one table to another.

Key properties:

Keys: primary key ↔ foreign key.
Cardinality: how rows in one table relate to rows in another (Section 18).
Cross-filter direction: single or both (Section 19).
Referential integrity (Section 16).
Active and inactive relationships: only one relationship between two tables can be active at a time. Additional relationships can exist but are inactive (shown as dashed lines in Model view) and do not filter unless a DAX measure activates them with USERELATIONSHIP. A relationship must exist and be active for columns from different tables to be used together.

18. Relationship Cardinality

Cardinality describes how data in one table relates to data in another, based on the count of matching rows. It should be distinguished from column cardinality, which refers to how unique the values in a single column are.

One-to-many (1:*), the most common type

A single row in one table matches multiple rows in the other.

Example: one patient (dimension) → many visits (fact); illustrative: one DimCustomer row → many FactSales rows.
Common in dimensional models because every dimension-to-fact link is of this type: the "one" side holds unique keys and the "many" side holds the events.

DimCustomer (1) ─────────────< FactSales (*)

Many-to-one is the same relationship read from the other direction.

One-to-one (1:1)

One row in a table links to only one row in another table. It arises when there is no separate information that justifies splitting the table, and it relates to normalisation.

Illustrative example: Employee and EmployeeBadge, with exactly one badge record per employee.
Before using it, consider whether the two tables should simply be combined into one (for example by a merge in Power Query). Filters flow in both directions in a 1:1 relationship.

Employee (1) ─────────── (1) EmployeeBadge

Many-to-many (:)

Multiple rows in one table link to multiple rows in another.

Illustrative example: students and courses.
It requires careful modelling because neither side has unique keys, so filters can match many rows on both sides and totals can become hard to interpret. A bridge (junction) table converts it into two one-to-many relationships.

Students (*) ───────────── (*) Courses
   safer:  Students (1)──<  Enrolments  >──(1) Courses

19. Filter Direction

Filter direction controls which table filters which when a user interacts with a slicer or visual.

Single-direction filtering

Filters flow from the "one" side (dimension) to the "many" side (fact).

DimProduct
    │
    ▼
FactSales

Selecting a product in a DimProduct slicer filters the corresponding rows in FactSales, so a revenue card then shows only that product's revenue (illustrative). This is the default for a one-to-many relationship and the preferred setting in a star schema.

Bidirectional filtering

DimProduct
    ▲
    ▼
FactSales

Filters flow in both directions. It should be used sparingly because it can cause:

ambiguous filter paths, where multiple routes between two tables leave it unclear which one Power BI should use, potentially deactivating a relationship or producing route-dependent results;
unnecessary complexity, since each bidirectional relationship makes the model harder to reason about;
unexpected filtering behaviour, as slicers begin filtering one another and numbers change in ways report users do not expect;
reduced performance on large models.

Guideline: keep relationships single-direction from dimension to fact, and use Both only for a specific, understood reason.

20. Establishing Relationships in Power BI

Identify the matching keys: the primary key in the dimension and the foreign key in the fact table.
Create the relationship by either:
- drag and drop, from primary key to foreign key in Model view; or
- Modeling → Manage relationships on the ribbon.
Set the cardinality and the cross-filter direction.

Preparing keys when none exist

When a flat table is split into dimensions, the following workflow applies:

Clean the data.
Create dimension tables, working out which columns belong to each dimension.
Give each dimension a unique identifier. If none exists, create one: right-click on the table → Add index column (from 1).

In the original table, find and replace the descriptive name with the unique index, which then serves as the foreign key.
Clean the data again and check for errors.
Create relationships and ensure they are active.
Calculate (DAX).
Display (dashboard).

The same result can also be achieved by merging the original table with the new dimension in Power Query, which leads to the next topic.

21. Joins in Power Query

Relationships connect tables inside the model. A join combines tables earlier, during data preparation. A join combines two tables using matching columns identified through keys, and is performed in Power Query because the table is being transformed to create a new, merged table.
In Power Query the command is Merge Queries. Which table's rows are kept in full depends on the join type.

Example used for all six joins

Illustrative example
Join key: CustomerID.

Customers (left table)

CustomerID	CustomerName
C1	Amina
C2	Brian
C3	Chebet

Orders (right table)

OrderID	CustomerID
O1	C1
O2	C1
O3	C4

Brian (C2) and Chebet (C3) have no orders, and order O3 belongs to C4, who is not in the Customers table.

21.1 Inner join

Keeps only matching records in both tables. Use when only records existing on both sides are wanted.

CustomerID	CustomerName	OrderID
C1	Amina	O1
C1	Amina	O2

Customers ∩ Orders         only the overlap

21.2 Left outer join

Keeps all rows from the left (first) table and only the matching rows from the right. Use to list all customers whether or not they ordered.

CustomerID	CustomerName	OrderID
C1	Amina	O1
C1	Amina	O2
C2	Brian	null
C3	Chebet	null

all of Customers + matching Orders

21.3 Right outer join

Keeps all rows from the right (second) table and the matching rows from the left.

CustomerID	CustomerName	OrderID
C1	Amina	O1
C1	Amina	O2
C4	null	O3

matching Customers + all of Orders

21.4 Full outer join

Keeps all records from both tables, whether or not they match.

CustomerID	CustomerName	OrderID
C1	Amina	O1
C1	Amina	O2
C2	Brian	null
C3	Chebet	null
C4	null	O3

Customers ∪ Orders         everything

21.5 Left anti join

Keeps only the rows in the left table that have no match in the right. Useful for finding customers who never ordered, and for data-quality checks.

CustomerID	CustomerName
C2	Brian
C3	Chebet

Customers − Orders         left only

21.6 Right anti join

Keeps only the rows in the right table that have no match in the left. Useful for finding orders whose customer is missing, which is a referential-integrity problem (Section 16).

OrderID	CustomerID
O3	C4

Orders − Customers         right only

Summary

Join	Rows kept	Result in the example
Inner	Matches only	2 rows
Left outer	All left + matching right	4 rows
Right outer	All right + matching left	3 rows
Full outer	Everything	5 rows
Left anti	Left rows with no match	2 rows
Right anti	Right rows with no match	1 row

22. Join Output Illustrations

 Left table = L,  Right table = R,  overlap = matched rows

 Inner       :        [ L ∩ R ]
 Left outer  :   [ L ........ ∩ ]       all of L, matched R
 Right outer :        [ ∩ ........ R ]   matched L, all of R
 Full outer  :   [ L ........ ∩ ........ R ]
 Left anti   :   [ L only ]
 Right anti  :                [ R only ]

23. Power Query Joins vs Power BI Relationships

Both mechanisms connect tables through matching keys, but they occur at different stages and do different things.

 Power Query                         Data model
 ───────────                         ──────────
 Merge / Join  ──►  Data           ──►  Relationships  ──►  DAX / Visuals
 (combine rows)     transformation       (connect tables)
 "preparation"                           "modelling"

Does a Power Query merge physically combine data? Yes. A merge creates a new table, or adds columns to an existing one, in which matched rows sit side by side. After Close & Apply, that wider table is what is loaded into the model.

Does a relationship physically combine tables? No. A relationship creates no new table and moves no data. Both tables remain separate. It is a rule in the model stating that when one table is filtered, the other is filtered through a shared key. Power BI applies it at calculation time, when a visual or measure needs columns from both tables.

When does each happen? Joins happen during preparation (Power Query); relationships are defined during modelling (Model view), after loading.

When is a merge preferable to a relationship?

To add a column from another table, such as bringing a county name into a table that only held a county code (illustrative).
To flatten a snowflake by merging sub-dimensions into one dimension.
To filter rows by existence in another table, using anti joins.
To bring a key into a fact table when creating dimensions.

A relationship is preferable when tables hold different kinds of information (dimension and fact) and should stay separate while still filtering one another.

What can excessive merging cause?

Wider tables with more columns.
Greater redundancy, since descriptions repeat on every row (the flat-table problem).
Loss of dimensional structure, because facts and dimensions blend into one table and clean, reusable dimensions disappear.
Higher model complexity and maintenance cost, and row multiplication if the key is not unique on one side of the merge.

Why do fact and dimension tables stay separate? A star schema works because dimensions filter and facts summarise. Merging everything into one table abandons that structure. Keeping the tables separate and relating them stores each description once, keeps the fact table focused on its grain, and keeps DAX simple.

	Power Query merge (join)	Power BI relationship
Stage	Data preparation	Data modelling
Location	Power Query Editor	Model view
Physically combines data?	Yes: creates a merged table	No: tables stay separate
Output	A new table or extra columns	A filter path between tables
Controlled by	Join kind (six types)	Cardinality and filter direction
When applied	At refresh / load	At query time, when visuals and measures run
Best for	Cleaning, enriching, flattening, checking	Linking facts to dimensions

In short: join during preparation; relate during modelling.

24. From Model to Visualisation

Once data has been cleaned, modelled, related and calculated, reports can be built. In Report view, a chart type is selected and the fields to plot are added. Visuals are interactive: selecting part of one visual filters the others, including KPIs, and even the components of a bar are interactive.

Visual	Purpose
Bar chart	Comparing categories
Pie / Donut / Treemap	Part-to-whole relationships; treemaps suit many categories
Scatter	Relationship between two numeric variables
Card	Displaying a KPI or single headline value (typically a measure)
Map	Geographic data (bubble, filled and shape maps)
Gauge / KPI	Targets and comparison
Slicer	Filtering by user selection

These visuals depend on the earlier stages. The card shows a CALCULATE measure (Section 7.4); the slicer filters through a relationship (Sections 17 and 19); chart categories come from clean dimension columns (Section 5); and the Total Revenue all county card (Section 7.5) shows how ALL makes a KPI ignore slicers deliberately.

25. Publishing to Power BI Service

Key terms

.PBIX: the Power BI Desktop file.
Semantic model: the entire data ecosystem: tables, relationships and connections, measures, and the model itself.
Report: the visuals, graphs and dashboards that report users read to evaluate the data.
Workspace: a collaborative area in Power BI Service where a team creates, manages and stores Power BI content.
Power BI Service: the browser version of Power BI, used to create, share and consume content.

What publishing does

Publishing sends the report and the semantic model from the .pbix file to the selected workspace.

Power BI Desktop (.pbix)
          │
          ▼  Home → Publish → select destination
Power BI Service
          │
          ▼
      Workspace
          │
          ▼
Semantic model  +  Report
          │
          ▼
Sharing / Collaboration  (workspace roles and security)

The semantic model is the same model built in Model view, now hosted in the cloud, so earlier modelling decisions affect every report built on it.

Steps

Before publishing: sign in to Power BI from the Desktop app, then sign in to Power BI Service online.
Create a workspace: open the left pane → Workspaces and complete the details (name, description, image, domain, Power BI Pro where external users need access, contact list).
Publish: in Desktop, Home → Publish → select destination.
Open the workspace and refresh to see the report and semantic model.
Share: subscriptions allow a particular report page to be shared with an intended person; a report with multiple pages can also be shared as a link.

26. Workspace Roles and Security

Workspace roles

Role	Description
Admin	Controls the workspace and access
Member	Can collaborate and publish into the workspace
Contributor	Can create and change content, with limited access functions
Viewer	Can consume content without editing

Exact permission lists should be confirmed against current Microsoft documentation.

Row-level security (RLS)

Row-level security controls which rows of data a user can see based on their assigned role, to keep data secure. Rules specify who needs access to what, and roles can be created before the report is shared and tested with View as.

Define roles and their DAX filter rules in Power BI Desktop (Modeling → Manage roles).
Publish, then assign users or groups to each role in Power BI Service. A defined role is not enforced until users are assigned.
RLS applies to Viewers. Members of the Admin, Member and Contributor roles have edit permission on the semantic model, so RLS does not restrict them.
Test with View as before sharing.

RLS filters travel across relationships like any other filter and by default follow single direction, which is a further reason to keep filter directions simple.

27. Recommended Power BI Model

For a typical business intelligence project, a star schema is the recommended design. The reasoning is that it fits how Power BI queries models and the types of questions BI projects ask, not that it is universally superior.

Criterion	Flat table	Star schema	Snowflake
Query / report performance	Repeated text bloats wide tables	Matches how Power BI visuals query the model; recommended by Microsoft	Extra hops between tables
DAX simplicity	Simple at first; awkward as questions grow	Simple: measures on facts, slicers from dimensions	More relationships to reason about
Readability	Hard to tell what each column represents	Clear: facts in the centre, dimensions around	Larger diagram
Scalability	Poor	Good: add a dimension or fact	Good, with added complexity
Data redundancy	High	Low	Lowest
Maintainability	Same value fixed in many rows	Fix a description once	Good for hierarchies; heavier to manage
Ease of report creation	One place for everything, but limited filtering	Easy: dimension fields for slicers and axes, fact fields for values	Builders must understand the chain
Filter propagation	Nothing to manage	Predictable: dimension → fact	Passes through several tables
Model complexity	Lowest initially	Moderate	Highest

When another design is appropriate

Flat table: a small, one-off analysis with one subject, where building dimensions costs more than it returns.
Snowflake: when a sub-dimension is genuinely shared (for example, a county table used by both patients and clinics) or the source is already normalised and flattening is not worthwhile. Merging the sub-tables in Power Query to obtain a simpler star should be considered first.

Recommended relationship design

One fact table at the centre, with a grain that can be stated in one sentence (for example, one row per visit).
One dimension table per descriptive subject (patient, doctor, department, date, location), each with a unique primary key.
Foreign keys in the fact table matching those primary keys. Where no natural key exists, create an index column and carry it into the fact table.
Cardinality: one-to-many (1:*) from each dimension to the fact table. Avoid many-to-many unless unavoidable, using a bridge table where needed, and consider merging one-to-one pairs into a single table.
Filter direction: single, from dimension to fact. Use Both only for a specific, understood reason, to avoid ambiguous paths.
Active relationships on the main path; an inactive relationship only where a measure deliberately uses USERELATIONSHIP.
Referential integrity checked in Power Query using a right anti join (fact keys with no dimension match) before loading.

 DimDepartment   DimDoctor
        \          /
         \        /
 DimPatient ── FactVisit ── DimDate
   (1)──────────►(*)         (1)──►(*)      single-direction, 1:* from every dimension

(Illustrative design based on the hospital example, with a date dimension added.)

28. The Complete Workflow

 1. Understand the problem / question
              ↓
 2. Identify the data source
              ↓
 3. Import / connect data
              ↓
 4. Inspect the raw data
              ↓
 5. Clean and transform with Power Query
              ↓
 6. Understand the structure of the data
              ↓
 7. Build the data model
              ↓
 8. Create fact and dimension tables
              ↓
 9. Establish relationships
              ↓
10. Apply appropriate cardinality / filter direction
              ↓
11. Use DAX to calculate and analyse
              ↓
12. Create visualisations
              ↓
13. Build the report
              ↓
14. Publish to Power BI Service
              ↓
15. Share, secure and collaborate

How the stages connect

Steps 1–3: The business question determines which data is needed and where it lives. Import brings a copy into Desktop.
Steps 4–5: Imported data is rarely analysis-ready. Data types, blanks, errors and duplicates are resolved in Power Query, with Applied Steps recording each action. Joins are available here for combining or checking tables.
Steps 6–8: Clean data is organised: measurements into a fact table with a clear grain, descriptions into dimension tables, usually in a star schema.
Steps 9–10: Primary keys in dimensions link to foreign keys in the fact table through one-to-many, single-direction relationships. The tables then work as one model without being merged.
Step 11: DAX operates on top of the model. CALCULATE, FILTER and ALL depend on filters moving through relationships, so the better the model, the simpler the DAX.
Steps 12–13: Visuals present measures and fields and respond to the filters defined in the model.
Steps 14–15: The .pbix is published to a workspace as a semantic model and report, with roles and RLS governing who can do and see what.

29. Conclusion

Power BI is best understood as a chain of dependent practices rather than a set of separate features. Data enters through connections to sources such as Excel, CSV, SQL and the web. Its quality must be established in Power Query before it can be trusted. DAX then turns clean data into answers, but its effectiveness depends on a well-structured model: fact tables with a defined grain, dimension tables with unique keys, one-to-many relationships and carefully chosen filter directions. Joins and relationships are complementary tools applied at different stages: joins combine data while it is being prepared, and relationships connect tables once they are loaded. Reports, dashboards and publishing sit at the end of the chain, so their reliability reflects the care taken in every earlier stage.

For most business intelligence projects, a star schema built on clean, correctly typed data offers the best balance of performance, simplicity, readability and maintainability.