Power BI

What is Power BI?

Power BI is a business intelligence tool made by Microsoft. It lets you:

• Connect to data (Excel, CSV, databases, websites, etc.)
• Clean and transform that data
• Build relationships between tables
• Create interactive visual reports and dashboards
• Share those reports with other people online

Step 1: Connecting to Data

The first thing you do in Power BI is connect to a data source. You go to:

Home → Get Data → choose your source

Sources we connected to in class:

• Excel workbooks
• CSV / text files
• SQL Server databases
• SharePoint lists
• Web URLs (yes, you can pull data directly from a website!)

Once connected, Power BI opens Power Query Editor where the real work begins.

Step 2: Cleaning Data in Power Query

Power Query is Power BI's built-in data cleaning tool. This is where you transform your raw, messy data into something usable before loading it into your model.

Everything you do here is recorded as steps on the right side panel called Applied Steps. This means you can undo any step at any time without losing your work.

Transformations we learned:

Transform	What it does	When to use it
Remove Duplicates	Deletes repeated rows	Cleaning ID or key columns
Fill Down	Fills blank cells with the value above	Fixing merged cell exports from Excel
Unpivot Columns	Turns column headers into row values	Reshaping wide data into tall data
Merge Queries	Joins two tables together (like SQL JOIN)	Combining related tables
Split Column	Splits one column into two by a delimiter	Separating first and last names
Change Data Type	Sets the correct type (text, number, date)	Making sure dates are read as dates
Add Custom Column	Creates a new column using an M formula	Row-level calculations at query stage
Remove Rows	Removes top rows, blank rows, errors	Cleaning header junk from messy files

Step 3: Data Modeling

After cleaning your data, you move to the Model view. This is where you define how your tables relate to each other.

The Star Schema

The recommended structure for Power BI models is called a **star schema:

• One central Fact table — this holds your transactions or events (sales, orders, payments)
• Multiple Dimension tables around it — these describe the facts (customers, products, dates, regions)

Relationships go from the dimension table to the fact table on a one-to-many basis.

Types of relationships:

Cardinality	Meaning	Example
One-to-many (1:*)	One record in table A matches many in table B	One customer → many orders
Many-to-many (:)	Both sides have multiple matches	Students ↔ courses (needs a bridge table)
One-to-one (1:1)	Each record matches exactly one	Employee → employee detail table

Cross filter direction
Relationships have a filter direction — either Single or Both. Single means filters flow one way (from dimension to fact). Both means filters flow both ways. We were told to stick with Single unless you have a specific reason to change it.

Date Table

One big thing we learned — always create a dedicated Date table. Mark it as a Date Table in the model settings. DAX time intelligence functions absolutely require this to work correctly.

Step 4: DAX — The Formula Language

DAX stands for Data Analysis Expressions. It's the language used to write formulas in Power BI. It looks similar to Excel but behaves very differently.

The biggest mindset shift: in Excel you write formulas for individual cells. In DAX, you write formulas for entire columns or measures, and the filter context changes the result depending on where it's used in the report.

DAX functions we covered:

Basic aggregations:

• SUM() — adds up a column
• AVERAGE() — average of a column
• COUNT() / COUNTROWS() — counts values or rows
• MIN() / MAX() — smallest or largest value
• DIVIDE() — safe division (handles divide by zero automatically)

Logical functions:

• IF() — if/else logic
• SWITCH() — cleaner alternative to nested IFs

Filter functions:

• CALCULATE() — the most important DAX function. Evaluates an expression in a modified filter context
• FILTER() — returns a filtered table
• ALL() — removes filters from a column or table
• ALLEXCEPT() — removes all filters except the ones you specify

Relationship functions:

• RELATED() — pulls a value from a related table (like VLOOKUP)
• RELATEDTABLE() — returns the related table

Time intelligence functions:

• TOTALYTD() — year-to-date total
• TOTALQTD() — quarter-to-date total
• SAMEPERIODLASTYEAR() — compares to the same period last year
• DATEADD() — shifts a date period forward or backward

CALCULATE was the hardest one to understand. Basically — it lets you change the filter context of any measure. Once you get it, everything else makes sense.

Step 5: Building Visuals

This is the fun part! The Report view is a drag-and-drop canvas. You place visuals, connect them to your measures and dimensions, and build your report page.

Visual types we used:

Visual	Best used for
Bar / Column chart	Comparing categories
Line chart	Showing trends over time
Pie / Donut chart	Part-to-whole (max 5 slices!)
Card visual	Displaying a single KPI number
Matrix	Pivot-table style breakdowns with subtotals
Table	Showing detailed row-level data
Map	Plotting values geographically
Slicer	Filter controls that users click on the canvas
Scatter chart	Showing correlation between two measures
Waterfall chart	Showing how values build up or break down

Design rules our lecturer gave us:

• One insight per visual — don't try to show everything in one chart
• Maximum 5 to 6 visuals per report page
• Always label your axes
• Use slicers to let users filter the report themselves
• Don't use pie charts with more than 5 slices — they become unreadable
• Consistent colours across the whole report
• If a stakeholder has to ask what they're looking at, the design has failed

Interactions between visuals

By default, clicking on one visual filters all the others on the page. You can customise this under Format → Edit interactions to control which visuals get filtered and which don't.

Step 6: Exporting / Downloading Your Table

Sometimes you need to get your cleaned data OUT of Power BI as a file.

Method 1 — From Power Query Editor:

1. Open Power Query Editor (Home → Transform data)
2. Select your table
3. Click File → Export → choose CSV
4. Save to your PC

Method 2 — From a Table Visual (most common):

1. Add a Table visual to your report canvas
2. Click the three dots (…) on the visual
3. Click Export data
4. Choose Underlying data → Export
5. A .xlsx or .csv downloads automatically

Method 3 — From Power BI Service:

1. Publish your report to app.powerbi.com
2. Open it in the browser
3. Hover over the visual → three dots (…) → Export data
4. Choose your format and download

Step 7: Publishing and Sharing

Once your report is done, you publish it to the Power BI Service.

Home → Publish → Select your Workspace

From the Service you can:

• Create a Dashboard by pinning tiles from your report
• Set a scheduled data refresh so your report stays up to date automatically
• Share the report with colleagues by email or link
• Embed the report into Microsoft Teams, SharePoint, or a website
• Set up Row-Level Security (RLS) so different users see different data

Dashboard vs Report — I kept confusing these:

• A Report has multiple pages, detailed visuals, full interactivity
• A Dashboard is a single page of pinned tiles — a quick overview for monitoring KPIs