Power BI for Data Analysts

What is Power BI?

Microsoft Power BI is a business intelligence platform that allows you to connect to data sources, clean and transform data, create reports and dashboards, and share insights with your organisation. It is one of the most widely used BI tools in enterprise environments, particularly in organisations that use the Microsoft ecosystem.

Power BI consists of several components:

• Power BI Desktop: A free Windows application for building reports and dashboards
• Power BI Service: The cloud-based platform (powerbi.com) for publishing, sharing, and collaborating
• Power BI Mobile: Apps for viewing reports on mobile devices

For analysts, Power BI Desktop is where you build; Power BI Service is where you share.

Getting Data into Power BI

Power BI can connect to virtually any data source. Common connections:

Import Mode

Data is copied from the source into Power BI's in-memory engine. Most common approach -- fast query performance, works offline.

Sources:

• Excel files (xlsx, csv)
• Databases (SQL Server, PostgreSQL, MySQL, Oracle)
• Cloud data warehouses (BigQuery, Snowflake, Redshift)
• APIs and web data
• SharePoint and OneDrive files
• Azure services

DirectQuery Mode

Queries run directly against the source database in real time. No data is stored in Power BI. Required when data is too large to import or when real-time data is needed. Slower query performance.

The Power BI Desktop Interface

Ribbon: Toolbar with Home, Insert, Modelling, View, and other tabs.

View Panes (left sidebar):

• Report View: Build visualisations
• Data View: Browse data in table format
• Model View: See relationships between tables

Fields Pane (right): Lists all tables and their columns. Drag fields onto the canvas to create visualisations.

Visualisations Pane (right): Select chart types and configure visual properties.

Canvas: The report page where you place and arrange visualisations.

Power Query: Data Transformation

Power Query (accessed via Transform Data in the Home ribbon) is Power BI's data cleaning and transformation tool. It has a graphical interface for common transformations:

• Rename columns
• Change data types
• Remove rows and columns
• Filter rows
• Merge queries (equivalent to SQL JOIN)
• Append queries (equivalent to UNION)
• Pivot and unpivot columns
• Add calculated columns

Every step is recorded in the "Applied Steps" panel on the right. This makes transformations reproducible and reviewable. When the data source updates, you click Refresh and all steps are re-applied automatically.

Example Power Query transformations:

1. Select column 'amount' > Transform > Data Type > Decimal Number
2. Select column 'country' > Transform > Format > TRIM (removes spaces)
3. Home > Remove Rows > Remove Blank Rows
4. Add Column > Custom Column: [unit_price] * [quantity]

The Data Model

Power BI uses a star schema data model:

• Fact tables: Contain the measures (revenue, quantity, cost) with foreign key references
• Dimension tables: Contain descriptive attributes (customer name, product category, date details)
• Relationships: Link tables via primary and foreign keys

Building a proper data model is critical for Power BI performance and accuracy. Go to Model View to see and configure relationships.

DAX: Data Analysis Expressions

DAX is the formula language used in Power BI for calculated columns and measures.

Calculated Measures (the most important concept)

Measures are dynamic calculations that respond to the filters applied in the report:

-- Total Revenue
Total Revenue = SUM(Sales[Amount])

-- Average Order Value
Avg Order Value = AVERAGE(Sales[Amount])

-- Order Count
Order Count = COUNT(Sales[OrderID])

-- Revenue vs Last Year
Revenue LY = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Calendar[Date]))

-- Year-over-Year Growth %
YoY Growth = DIVIDE([Total Revenue] - [Revenue LY], [Revenue LY])

Calculated Columns

Static calculations that add a column to a table (calculated once when data loads):

-- Revenue per order item
Line Total = Sales[Quantity] * Sales[UnitPrice]

-- Customer segment
Segment = IF(Sales[Revenue] >= 1000, "VIP",
           IF(Sales[Revenue] >= 500, "Regular", "Standard"))

Best practice: Prefer measures over calculated columns. Measures are calculated dynamically at query time and respond to filters. Columns are static.

Building a Report

Add a visualisation:

1. Click a blank area on the canvas
2. Click a chart type in the Visualisations pane
3. Drag fields from the Fields pane into the chart's Field Wells (Axis, Values, Legend, etc.)

Add slicers (interactive filters):

1. Click the Slicer visual in the Visualisations pane
2. Drag a dimension field (e.g., Country, Date) into the slicer field well
3. The slicer filters all visuals on the same page

Cross-filtering:

By default, clicking a data point in one visual filters all other visuals on the page. This is a key Power BI feature that makes reports interactive.

Publishing and Sharing

1. In Power BI Desktop: File > Publish > To Power BI Service
2. In Power BI Service: Create a Dashboard by pinning visuals from reports
3. Share a report via: Share button > enter email addresses
4. Create an App to package multiple reports for a specific audience

Data refresh: Configure automatic data refresh in Power BI Service to keep published reports current. Supports scheduled refresh up to 8 times per day on the Pro license.

Key Takeaways

• Power BI Desktop is free and used for building; Power BI Service is the cloud platform for publishing and sharing.
• Power Query records every data transformation step, making cleaning reproducible and refreshable when data updates.
• Build a proper star schema data model (fact tables + dimension tables linked by relationships) for accurate and performant reports.
• Measures (DAX calculations that respond to filters) are the most important concept in Power BI -- prefer them over calculated columns.
• Cross-filtering and slicers make Power BI reports interactive: clicking any data point or slicer button filters all visuals on the page.

Practice Exercise

Download Power BI Desktop (free from powerbi.microsoft.com). Complete the following:

1. Import the sample Contoso data (available in Power BI Desktop under Sample datasets)
2. In Power Query: remove blank rows, rename a column, and add a calculated column
3. In the Model View: verify the relationships between tables are correctly defined
4. Create a report page with: one line chart (sales over time), one bar chart (sales by product category), and one slicer (by year)
5. Add a DAX measure for Total Revenue and one for YoY Growth %
6. Publish the report to Power BI Service (requires a free account)