Nabid In Motion Nabid In Motion

Study interactive :: Progress tools open in the Study Hub reader.

Power BI for Data Analysis

Microsoft Power BI for data visualization, analysis, and business intelligence.

Table of Contents

Introduction & Data Analyst Foundation

Discover Data Analysis

Roles in Data:

Tasks of a Data Analyst: The data analyst workflow follows these steps:

  1. Prepare → Clean and transform data (Power Query)
  2. Model → Create relationships and data model (Power Pivot)
  3. Visualize → Create charts and reports (Power View)
  4. Analyze → Build calculations and insights (DAX)
  5. Manage → Share and maintain reports (Power BI Service)

Workflow Table:

Step Tool Used Purpose
Prepare Power Query Clean, transform, and shape data
Model Power Pivot Create relationships and data model
Visualize Power View Create interactive visualizations
Analyze DAX Build calculations and measures
Share Power BI Service Publish and share reports

Power BI Ecosystem Overview

Power BI Desktop:

Power BI Service:

Power BI Mobile:

Power BI Report Server:

Data Analyst Workflow & Use Cases in Business

Common Use Cases:

Workflow Example:

  1. Connect to sales database
  2. Transform data (clean, merge, calculate)
  3. Model relationships (Products, Customers, Dates)
  4. Visualize sales trends and KPIs
  5. Analyze with DAX measures
  6. Share dashboard with stakeholders

Power BI Building Blocks

Core Concepts:

Datasets:

Reports:

Dashboards:

Workspaces:

Semantic Models vs Visual Layers

Semantic Model (Data Layer):

Visual Layer:

Key Concept: One semantic model can support multiple reports and dashboards.

Power BI Roles in Microsoft Fabric Ecosystem

Microsoft Fabric: Unified analytics platform integrating Power BI with other services.

Power BI in Fabric:

Roles:

Introduction to Power BI

What is Power BI?

Power BI is a business analytics service by Microsoft that provides interactive visualizations and business intelligence capabilities with an interface simple enough for end users to create their own reports and dashboards.

Key Features:

Power BI Components

  1. Power BI Desktop: Free desktop application for creating reports
  2. Power BI Service: Cloud-based service for sharing and collaboration
  3. Power BI Mobile: Mobile apps for iOS, Android, Windows
  4. Power BI Report Server: On-premises reporting solution

When to Use Power BI

Use Power BI when:

Alternatives:

Get Started with Power BI

Installation, Setup, and Interface Overview

Installation:

  1. Download Power BI Desktop from powerbi.microsoft.com
  2. Run installer
  3. Sign in with Microsoft account (optional, for publishing)

Initial Setup:

  1. File → Options and Settings → Options
  2. Configure:
    • Regional Settings: Date/time formats
    • Privacy: Data source privacy levels
    • DirectQuery: Query timeout settings
    • R Scripting: R installation path (if using R)

Interface Overview:

Connecting Data Sources

Supported Data Sources:

Steps to Connect:

  1. Home → Get Data
  2. Select data source
  3. Enter connection details
  4. Select tables/queries
  5. Transform Data or Load

Example - Excel:

Get Data → Excel → Select file → Choose sheets → Load

Example - SQL Server:

Get Data → SQL Server → Enter server → Select database → Choose tables → Load

Basic Transformations in Power Query

Common Transformations:

Remove Rows:

Split Columns:

Fill Down:

Data Types:

Rename Columns:

Detect Data Type Errors:

Semantic Models vs Visual Layers

Semantic Model (Backend):

Visual Layer (Frontend):

Best Practice: Build robust semantic model first, then create visualizations.

Understanding Power BI Building Blocks

Datasets:

Reports:

Dashboards:

Workspaces:

Understanding the Power BI Interface

Power BI Desktop Layout

Main Areas:

  1. Ribbon: Contains tabs (Home, Insert, Modeling, View)
  2. Fields Pane: Lists all tables and fields from your data
  3. Visualizations Pane: Choose visualization types
  4. Filters Pane: Apply filters to visuals and pages
  5. Canvas: Where you build your visualizations
  6. Report View: Main view for creating reports
  7. Data View: View and edit data tables
  8. Model View: Manage relationships between tables

Getting Started

Step 1: Import Data

Home Tab → Get Data → Choose data source

Step 2: Transform Data

Home Tab → Transform Data → Power Query Editor opens

Step 3: Create Visualizations

Drag fields from Fields pane to Canvas
Select visualization type from Visualizations pane

Step 4: Format Visuals

Use Format pane to customize colors, fonts, titles

Power BI Visualizations

Basic Visualizations

1. Bar Chart

Use for: Comparing categories

How to Create:

  1. Select Bar Chart from Visualizations
  2. Drag category to Axis
  3. Drag measure to Values

Example:

2. Line Chart

Use for: Showing trends over time

How to Create:

  1. Select Line Chart
  2. Drag date to Axis
  3. Drag measure to Values

Example:

3. Pie Chart

Use for: Showing proportions

How to Create:

  1. Select Pie Chart
  2. Drag category to Legend
  3. Drag measure to Values

Example:

4. Scatter Chart

Use for: Showing relationships between two measures

How to Create:

  1. Select Scatter Chart
  2. Drag measure to X Axis
  3. Drag measure to Y Axis
  4. Optionally add Size and Legend

Example:

5. Table

Use for: Showing detailed data

How to Create:

  1. Select Table
  2. Drag fields to Values

Example:

6. Matrix

Use for: Pivot table-like analysis

How to Create:

  1. Select Matrix
  2. Drag fields to Rows
  3. Drag fields to Columns
  4. Drag measures to Values

Example:

Advanced Visualizations

7. Map Visualizations

Use for: Geographic analysis

8. Gauge

Use for: Showing progress toward a goal

Example:

9. KPI

Use for: Key Performance Indicators

Example:

10. Waterfall Chart

Use for: Showing cumulative effect of positive and negative values

Example:

Core Visuals

Bar Chart:

Column Chart:

Line Chart:

Pie/Donut Chart:

Scatter Chart:

Card & Multi-row Card:

Table & Matrix:

Advanced Visuals

Waterfall Chart:

Map (Filled):

Tree Map:

Gauge:

KPI:

Funnel:

Conditional Formatting and Custom Tooltips

Conditional Formatting: Apply formatting based on values.

Types:

Steps:

  1. Select visual
  2. Format visual → Conditional formatting
  3. Choose field
  4. Configure rules

Custom Tooltips: Enhanced tooltips with additional visuals.

Steps:

  1. Create tooltip page
  2. Build small visual
  3. Format page → Tooltip
  4. Assign to visual

Use Cases:

Custom Visualizations

Power BI supports custom visuals from the marketplace:

  1. Chiclet Slicer: Enhanced filtering
  2. Infographic Designer: Custom infographics
  3. Synoptic Panel: Custom geographic maps
  4. Word Cloud: Text analysis visualization

How to Add:

Visualizations pane → Get more visuals → Import from marketplace

Power BI Filtering and Interactivity

Types of Filters

1. Visual-Level Filters

Apply to a single visualization

How to Use:

  1. Select a visual
  2. Go to Filters pane
  3. Expand the visual name
  4. Add filters to fields

Example:

2. Page-Level Filters

Apply to all visuals on a page

How to Use:

  1. Go to Filters pane
  2. Expand "Filters on this page"
  3. Add filters

Example:

3. Report-Level Filters

Apply to all pages in the report

How to Use:

  1. Go to Filters pane
  2. Expand "Filters on all pages"
  3. Add filters

Example:

4. Slicers

Interactive filters that users can control

How to Create:

  1. Select Slicer from Visualizations
  2. Drag field to Field

Types of Slicers:

Example:

// Slicer for Date Range
// Users can select start and end dates

Cross-Filtering and Cross-Highlighting

Cross-Filtering: Selecting a data point filters other visuals Cross-Highlighting: Selecting a data point highlights related data

How to Configure:

  1. Select a visual
  2. Format pane → Edit interactions
  3. Choose: Filter, Highlight, or None

Design Power BI Report

Power BI Report Structure:

Report Objects:

Select Appropriate Visual Type:

Format and Configure Visualizations:

Interactive Features:

Enhance Reports for User Experience

Report Navigation:

Filtering:

Report Elements:

Bookmarks: Save current state of report.

Steps:

  1. View → Bookmarks pane
  2. Set up report state
  3. Add bookmark
  4. Use button to navigate

Use Cases:

Drill Through: Navigate to detail page with context.

Steps:

  1. Create detail page
  2. Set up drill-through fields
  3. Right-click visual → Drill through
  4. Configure fields

Example:

Hierarchies & Drill Down: Enable drill-down in visuals.

Steps:

  1. Create hierarchy
  2. Add to visual
  3. Enable drill-down
  4. Users can expand/collapse

Bookmarks & Buttons: Create interactive navigation.

Steps:

  1. Create bookmarks
  2. Insert → Button
  3. Assign bookmark to button
  4. Style button

Dynamic Filters with Slicers: Interactive filtering experience.

Slicer Types:

Sync Slicers:

  1. View → Sync Slicers
  2. Select slicers to sync
  3. Choose pages to sync

Report Themes & Color Palettes: Consistent styling.

Steps:

  1. View → Themes → Customize theme
  2. Set colors, fonts
  3. Apply to report

Color Palettes:

Drill-Through

Allow users to drill into details from summary data

How to Set Up:

  1. Create detail page
  2. Right-click on visual → Drill through
  3. Configure drill-through fields

Example:

Choose When to Use Paginated Reports

Paginated Reports: Pixel-perfect reports for printing/PDF.

When to Use:

Power BI Reports vs Paginated Reports:

DAX (Data Analysis Expressions)

Introduction to DAX

DAX is a formula language used in Power BI, Power Pivot, and Analysis Services. It's similar to Excel formulas but designed for data modeling.

DAX Syntax

MeasureName = FUNCTION(Table[Column], [Filter1], [Filter2])

Basic DAX Functions

1. Aggregation Functions

SUM:

Total Sales = SUM(Sales[Amount])

AVERAGE:

Average Sales = AVERAGE(Sales[Amount])

COUNT:

Total Orders = COUNT(Sales[OrderID])

COUNTROWS:

Number of Products = COUNTROWS(Products)

MIN/MAX:

Min Sales = MIN(Sales[Amount])
Max Sales = MAX(Sales[Amount])

2. Filter Functions

CALCULATE: Modify filter context

Sales 2023 = CALCULATE(SUM(Sales[Amount]), Sales[Year] = 2023)

FILTER: Filter a table

High Value Sales = 
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(Sales, Sales[Amount] > 1000)
)

ALL: Remove filters

Total Sales All Time = 
CALCULATE(SUM(Sales[Amount]), ALL(Sales))

3. Time Intelligence Functions

TOTALYTD: Year-to-date total

Sales YTD = TOTALYTD(SUM(Sales[Amount]), 'Date'[Date])

SAMEPERIODLASTYEAR: Compare to previous year

Sales PY = 
CALCULATE(
    SUM(Sales[Amount]),
    SAMEPERIODLASTYEAR('Date'[Date])
)

DATEDIFF: Calculate difference between dates

Days Since Order = 
DATEDIFF(Orders[OrderDate], TODAY(), DAY)

4. Text Functions

CONCATENATE: Combine text

Full Name = CONCATENATE(Customers[FirstName], " ", Customers[LastName])

LEFT/RIGHT: Extract characters

First 3 Chars = LEFT(Products[ProductCode], 3)

UPPER/LOWER: Change case

Upper Name = UPPER(Customers[Name])

5. Logical Functions

IF: Conditional logic

Sales Category = 
IF(
    SUM(Sales[Amount]) > 10000,
    "High",
    "Low"
)

SWITCH: Multiple conditions

Priority Level = 
SWITCH(
    TRUE(),
    Orders[Amount] > 1000, "High",
    Orders[Amount] > 500, "Medium",
    "Low"
)

Add Measures to Semantic Models

Calculated Measures: Aggregations computed on-the-fly.

Steps:

  1. Modeling → New Measure
  2. Enter DAX formula
  3. Name the measure

Example:

Total Sales = SUM(Sales[Amount])
Average Sales = AVERAGE(Sales[Amount])

Calculated Columns: Row-level calculations stored in memory.

Steps:

  1. Modeling → New Column
  2. Enter DAX formula

Example:

Full Name = Customers[FirstName] & " " & Customers[LastName]
Profit = Sales[Revenue] - Sales[Cost]

Calculated Tables: New tables created with DAX.

Steps:

  1. Modeling → New Table
  2. Enter DAX formula

Example:

Sales Summary = 
SUMMARIZE(
    Sales,
    Sales[ProductID],
    "Total Sales", SUM(Sales[Amount])
)

Row Context vs Filter Context

Row Context: Context of current row (for calculated columns).

// Calculated Column (Row Context)
Profit = Sales[Revenue] - Sales[Cost]
// Evaluates for each row

Filter Context: Filters applied to calculation (for measures).

// Measure (Filter Context)
Total Sales = SUM(Sales[Amount])
// Respects filters from visuals, slicers, etc.

Understanding Context:

Dynamic Titles, KPIs, Variance %

Dynamic Titles: Titles that change based on selections.

// Measure for dynamic title
Title = 
"Sales by " & SELECTEDVALUE(Products[Category], "All Categories")

KPIs: Key Performance Indicators with targets.

Steps:

  1. Create measure for value
  2. Create measure for target
  3. Use KPI visual
  4. Set status thresholds

Example:

Sales KPI = SUM(Sales[Amount])
Sales Target = 100000

// Status
Sales Status = 
IF(
    [Sales KPI] >= [Sales Target],
    "On Target",
    "Below Target"
)

Variance %: Calculate percentage difference.

Sales Variance % = 
VAR CurrentSales = SUM(Sales[Amount])
VAR PreviousSales = 
    CALCULATE(
        SUM(Sales[Amount]),
        SAMEPERIODLASTYEAR(Date[Date])
    )
RETURN
    DIVIDE(
        CurrentSales - PreviousSales,
        PreviousSales,
        0
    )

Optimize Model Performance

Use Variables (VAR, RETURN): Improve performance and readability.

// Without VAR (calculates twice)
Total Sales = 
IF(
    SUM(Sales[Amount]) > 1000,
    SUM(Sales[Amount]) * 0.1,
    SUM(Sales[Amount]) * 0.05
)

// With VAR (calculates once)
Total Sales = 
VAR TotalAmount = SUM(Sales[Amount])
RETURN
    IF(
        TotalAmount > 1000,
        TotalAmount * 0.1,
        TotalAmount * 0.05
    )

Performance Tips:

  1. Use measures instead of calculated columns when possible
  2. Use VAR to avoid multiple calculations
  3. Filter early in CALCULATE
  4. Review relationship cardinality
  5. Use aggregations for large datasets
  6. Monitor with Performance Analyzer

Create Visual Calculations

Visual Calculations: Calculations defined at visual level (Power BI 2024+).

Understanding Visual Calculations:

Creating Visual Calculations:

  1. Select visual
  2. Analytics pane → Visual calculations
  3. Add calculation
  4. Use DAX-like syntax

Example:

Running Total = RUNNINGSUM([Sales])
Previous Period = PREVIOUS([Sales])

Use Cases:

Parameters in Visual Calculations: Use parameters for flexibility.

Case Study: Financial Dashboard using DAX

Components:

Calculated Columns vs Measures

Calculated Columns:

Example:

// Calculated Column
Full Name = Customers[FirstName] & " " & Customers[LastName]

Measures:

Example:

// Measure
Total Sales = SUM(Sales[Amount])

Data Modeling and DAX Essentials

Design a Semantic Model

Understanding Relational Data Modeling:

Relational Model:

Key Concepts:

Star Schema vs Snowflake Schema Design

Star Schema: Simplified structure with fact table and dimension tables.

Structure:

FactSalesDimProductDimCustomerDimDateDimRegion

Advantages:

Snowflake Schema: Normalized dimensions with sub-dimensions.

Structure:

FactSalesDimProductDimCategoryDimSubcategoryDimCustomerDimGeographyDimDate

When to Use:

Best Practice: Prefer Star Schema for Power BI.

Managing Relationships

Relationship Types:

One-to-Many (1:Many):

Many-to-Many:

One-to-One:

Creating Relationships:

  1. Model View
  2. Drag from one table to another
  3. Configure properties:
    • Cardinality: One-to-many, Many-to-one, etc.
    • Cross Filter Direction: Single or Both
    • Make This Relationship Active: Check/uncheck

Cardinality & Cross Filter Direction

Cardinality: Defines relationship type.

Options:

Cross Filter Direction: Controls how filters flow.

Single:

Both:

Example:

Sales → Product (Many-to-One, Single)
Sales → Date (Many-to-One, Single)

Role of Primary & Foreign Keys

Primary Key (PK):

Foreign Key (FK):

Best Practices:

Building Hierarchies

Date Hierarchy:

Date Hierarchy
├── Year
├── Quarter
├── Month
└── Day

Product Hierarchy:

Product Hierarchy
├── Category
├── Subcategory
└── Product

Geography Hierarchy:

Geography Hierarchy
├── Country
├── Region
├── State
└── City

Creating Hierarchies:

  1. Model View
  2. Right-click table → New Hierarchy
  3. Drag columns into hierarchy
  4. Reorder as needed

Use Cases:

Handling Inactive Relationships

Inactive Relationships: Multiple relationships between same tables.

Example:

Using USERELATIONSHIP:

Sales by Ship Date = 
CALCULATE(
    SUM(Sales[Amount]),
    USERELATIONSHIP(Sales[ShipDate], Date[Date])
)

Best Practice: Use inactive relationships for different date perspectives.

Project: Build a Complete Power Pivot Model

Steps:

  1. Import fact table (Sales)
  2. Import dimension tables (Product, Customer, Date)
  3. Create relationships
  4. Set up hierarchies
  5. Create measures
  6. Build visualizations

Advanced DAX and Data Modeling

Advanced DAX Patterns

1. Running Totals

Running Total = 
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(
        ALL('Date'[Date]),
        'Date'[Date] <= MAX('Date'[Date])
    )
)

2. Percentage of Total

% of Total Sales = 
DIVIDE(
    SUM(Sales[Amount]),
    CALCULATE(SUM(Sales[Amount]), ALL(Sales))
)

3. Moving Averages

Moving Average 7 Days = 
AVERAGEX(
    DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -7, DAY),
    CALCULATE(SUM(Sales[Amount]))
)

4. Rank Functions

Sales Rank = 
RANKX(
    ALL(Products),
    CALCULATE(SUM(Sales[Amount]))
)

Data Modeling Best Practices

1. Star Schema Design

Example:

FactSales
├── ProductID (FK)
├── CustomerID (FK)
├── DateID (FK)
├── SalesAmount (Measure)
└── Quantity (Measure)

DimProduct
├── ProductID (PK)
├── ProductName
└── Category

DimCustomer
├── CustomerID (PK)
├── CustomerName
└── Region

DimDate
├── DateID (PK)
├── Date
├── Year
└── Month

2. Relationships

How to Create:

  1. Model View
  2. Drag from one table to another
  3. Configure relationship properties

3. Hierarchies

Create drill-down paths

Example:

Date Hierarchy
├── Year
├── Quarter
├── Month
└── Day

4. Calculated Tables

Create new tables using DAX

Example:

// Calculated Table
Sales Summary = 
SUMMARIZE(
    Sales,
    Sales[ProductID],
    "Total Sales", SUM(Sales[Amount]),
    "Order Count", COUNT(Sales[OrderID])
)

Data Preparation (Power Query Mastery)

Get Data. Storage Modes

Storage Mode Options:

Import:

DirectQuery:

Dual:

DirectLake (Fabric):

When to Use:

Data Profiling

Column Profiling:

Column Quality:

Column Distribution:

Resolve Inconsistencies and Data Quality Issues

Common Issues:

Solutions:

Data Shape Transformations

Column Operations:

Table Operations:

User-Friendly Naming Conventions

Best Practices:

Example:

Bad: "Sales_Data_2024_Final_v2"
Good: "Sales2024"

Bad: "Customer Name"
Good: "CustomerName" or "Customer_Name"

Merge Queries vs Append Queries

Merge Queries: Combine data horizontally (like SQL JOIN).

Steps:

  1. Home → Merge Queries
  2. Select two tables
  3. Choose join type (Inner, Left, Right, Full Outer)
  4. Select matching columns
  5. Expand merged columns

Use Cases:

Append Queries: Stack tables vertically (like SQL UNION).

Steps:

  1. Home → Append Queries
  2. Select tables to append
  3. Choose "Two tables" or "Three or more tables"

Use Cases:

Conditional Columns and Custom Columns

Conditional Columns: Add column based on conditions.

Steps:

  1. Add Column → Conditional Column
  2. Define conditions
  3. Set output values

Example:

If [Sales] > 1000 then "High"
Else if [Sales] > 500 then "Medium"
Else "Low"

Custom Columns: Add column using M language.

Steps:

  1. Add Column → Custom Column
  2. Enter M formula
  3. Name the column

Example:

// M Language
[FirstName] & " " & [LastName]

Group By and Aggregate

Group By: Aggregate data by groups.

Steps:

  1. Transform → Group By
  2. Select grouping column(s)
  3. Choose aggregation (Sum, Average, Count, etc.)
  4. Select column to aggregate

Example:

Pivot & Unpivot Columns

Pivot Column: Transform rows to columns.

Steps:

  1. Transform → Pivot Column
  2. Select value column
  3. Select attribute column
  4. Choose aggregation

Unpivot Columns: Transform columns to rows.

Steps:

  1. Transform → Unpivot Columns
  2. Select columns to unpivot
  3. Creates Attribute and Value columns

Handling Errors & Nulls like a Pro

Error Handling:

Null Handling:

Best Practices:

Parameters & Functions in Power Query

Parameters: Reusable values in queries.

Create Parameter:

  1. Manage Parameters → New Parameter
  2. Define name, type, value
  3. Use in queries: #"Parameter Name"

Example:

Parameter: StartDate
Type: Date
Value: 2024-01-01

Use in filter: [Date] >= #"StartDate"

Functions: Reusable M code.

Create Function:

  1. Create query with parameters
  2. Right-click → Create Function
  3. Define parameters
  4. Use in other queries

Example:

// Function: CleanText
(text as text) =>
    Text.Trim(Text.Clean(text))

Dynamic Source Switching (Folder + File Parameterization)

Folder Parameterization: Load all files from folder.

Steps:

  1. Get Data → Folder
  2. Select folder
  3. Combine files
  4. Power Query creates function

File Parameterization: Switch between files dynamically.

Steps:

  1. Create parameter for file path
  2. Use parameter in source
  3. Change parameter to switch files

Example:

// Parameter: FilePath
// Source
Excel.Workbook(File.Contents(#"FilePath"))

Creating Date Table using M Language

M Language Date Table:

let
    StartDate = #date(2020, 1, 1),
    EndDate = #date(2024, 12, 31),
    NumberOfDays = Duration.Days(EndDate - StartDate),
    DateList = List.Dates(StartDate, NumberOfDays + 1, #duration(1, 0, 0, 0)),
    DateTable = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    RenamedColumns = Table.RenameColumns(DateTable, {{"Column1", "Date"}}),
    ChangedType = Table.TransformColumnTypes(RenamedColumns, {{"Date", type date}}),
    InsertYear = Table.AddColumn(ChangedType, "Year", each Date.Year([Date]), Int64.Type),
    InsertM "Month", each Date.Month([Date]), Int64.Type),
    InsertDay = Table.AddColumn(InsertMonth, "Day", each Date.Day([Date]), Int64.Type),
    InsertQuarter = Table.AddColumn(InsertDay, "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    InsertWeekday = Table.AddColumn(InsertQuarter, "Weekday", each Date.DayOfWeek([Date]), Int64.Type),
    InsertWeekNumber = Table.AddColumn(InsertWeekday, "WeekNumber", each Date.WeekOfYear([Date]), Int64.Type)
in
    InsertWeekNumber

Or Use DAX:

// Calculated Table
DateTable = 
CALENDAR(DATE(2020,1,1), DATE(2024,12,31))

Query Diagnostics (Performance Optimization)

Enable Diagnostics:

  1. View → Query Diagnostics → Start Diagnostics
  2. Run query
  3. View → Query Diagnostics → Stop Diagnostics

Analyze Results:

Optimization Tips:

Case Study: Sales Data Cleaning + Auto Refresh Setup

Scenario: Clean messy sales data and set up automatic refresh.

Steps:

  1. Connect to Excel file
  2. Remove header rows
  3. Split product codes
  4. Clean customer names
  5. Fix date formats
  6. Remove duplicates
  7. Create date table
  8. Set up parameters for file path
  9. Configure refresh schedule in Power BI Service

Power Query

Introduction to Power Query

Power Query is the data transformation engine in Power BI. It's used to connect, combine, and refine data from multiple sources.

Power Query Editor Interface

Main Areas:

  1. Query Pane: List of queries
  2. Data Preview: Preview of transformed data
  3. Ribbon: Transformation commands
  4. Formula Bar: M language formulas
  5. Applied Steps: History of transformations

Common Transformations

1. Remove Columns

Home → Remove Columns

2. Rename Columns

Transform → Rename

3. Change Data Types

Transform → Data Type

4. Remove Rows

Home → Remove Rows
- Remove Top Rows
- Remove Bottom Rows
- Remove Alternate Rows
- Remove Blank Rows
- Remove Duplicates

5. Split Columns

Transform → Split Column
- By Delimiter
- By Number of Characters
- By Positions

6. Add Columns

Add Column → Custom Column

Example:

// M Language Formula
[FirstName] & " " & [LastName]

7. Group By

Transform → Group By

Example:

8. Pivot/Unpivot

Transform → Pivot Column
Transform → Unpivot Columns

Advanced Power Query

1. Merge Queries

Combine data from multiple tables

How to Use:

  1. Home → Merge Queries
  2. Select two tables
  3. Choose join type (Inner, Left, Right, Full Outer)
  4. Select matching columns

Example:

2. Append Queries

Stack tables vertically

How to Use:

  1. Home → Append Queries
  2. Select tables to append

Example:

3. Parameters

Create reusable parameters

How to Create:

  1. Manage Parameters → New Parameter
  2. Define name, type, and value

Example:

4. Custom Functions

Create reusable M functions

Example:

// Custom Function: Clean Text
(text as text) =>
    Text.Trim(Text.Clean(text))

Advanced Data Transformation and Integration

Data Source Integration

1. SQL Server

Get Data → SQL Server
- Enter server name
- Enter database name
- Choose authentication method

2. Excel Files

Get Data → Excel
- Select file
- Choose sheets to import

3. CSV Files

Get Data → Text/CSV
- Select file
- Configure delimiter

4. Web Data

Get Data → Web
- Enter URL
- Power Query extracts tables

5. APIs

Get Data → Web
- Enter API endpoint
- Configure authentication

Incremental Refresh

Load only new or changed data

How to Set Up:

  1. Model → Manage Relationships
  2. Configure incremental refresh policy
  3. Define date range

Example:

Data Refresh

Scheduled Refresh:

  1. Power BI Service
  2. Dataset Settings
  3. Schedule Refresh
  4. Set frequency and time

Manual Refresh:

Home → Refresh

Management, Security, and Microsoft Fabric

Create and Manage Workspaces

Create Workspace:

  1. Power BI Service
  2. Workspaces → Create workspace
  3. Enter name and description
  4. Choose workspace type

Workspace Types:

Manage Permissions:

  1. Workspace → Access
  2. Add users/groups
  3. Assign roles:
    • Admin: Full control
    • Member: Contribute
    • Contributor: Add content
    • Viewer: View only

Workspace Items:

Share and Distribute Reports:

Implement Row Level Security (RLS)

RLS (Static Method): Define rules for specific users.

Steps:

  1. Power BI Desktop → Modeling → Manage Roles
  2. Create role
  3. Define DAX filter
  4. Assign users to role

Example:

// Role: Regional Manager
[Region] = "North America"

RLS (Dynamic Method): Use user information in filters.

Steps:

  1. Create role
  2. Use USERPRINCIPALNAME() or USERNAME()
  3. Match to data

Example:

// Dynamic RLS
[SalespersonEmail] = USERPRINCIPALNAME()

Testing RLS:

  1. Modeling → View As
  2. Select role
  3. Test report view

Manage Semantic Models

Connect to On-premises Data Sources:

  1. Install Power BI Gateway
  2. Configure gateway
  3. Add data source
  4. Use in Power BI Service

Keep Data Up to Date using Incremental Refresh: Load only new/changed data.

Steps:

  1. Configure incremental refresh policy
  2. Define date range
  3. Set refresh frequency
  4. Historical data range

Example:

Semantic Model Endorsement: Promote quality datasets.

Levels:

Data Protection & Report Settings:

Leverage Usage Metrics Reports: Track report usage.

Steps:

  1. Workspace → Usage metrics
  2. View report usage
  3. Identify popular reports
  4. Optimize based on usage

Create Dashboards

Create Power BI Dashboards:

  1. Power BI Service
  2. Workspace → New → Dashboard
  3. Name dashboard
  4. Pin visuals

Pin Live Report Pages:

  1. Open report
  2. Pin visual or page
  3. Choose dashboard
  4. Visual updates automatically

Add Theme to Visuals:

  1. Dashboard → Edit
  2. Format → Theme
  3. Choose theme
  4. Apply to dashboard

Set Mobile View:

  1. Dashboard → Edit
  2. Mobile layout
  3. Arrange tiles for mobile
  4. Optimize for small screens

Explore Copilot

Understand Semantic Model Requirements:

Create Visuals and Reports using Copilot:

  1. Power BI Service
  2. Create → Copilot
  3. Describe what you want
  4. Copilot generates report

Example Prompts:

Create Summaries using Copilot:

  1. Select report
  2. Ask Copilot to summarize
  3. Review AI-generated summary
  4. Refine if needed

Explore End-to-End Analytics with Microsoft Fabric

Describe End-to-End Analytics in Microsoft Fabric: Unified platform for analytics.

Components:

Understand Data Teams and Roles:

Describe How to Enable and Use Fabric:

  1. Enable Fabric in tenant
  2. Create Fabric workspace
  3. Use Fabric services
  4. Integrate with Power BI

Fabric Integration:

Power BI Services

Power BI Service Overview

Power BI Service is the cloud-based platform for sharing and collaborating on Power BI reports.

Key Features

1. Workspaces

Organize reports and dashboards

Types:

2. Dashboards

Single-page view of multiple visuals

How to Create:

  1. Pin visuals from reports
  2. Arrange on dashboard
  3. Add tiles and widgets

3. Apps

Packaged collections of dashboards and reports

How to Publish:

  1. Workspace → Create App
  2. Configure settings
  3. Publish to organization

4. Sharing

Share reports with users

Methods:

Row-Level Security (RLS)

Restrict data access based on user roles

How to Set Up:

  1. Model → Manage Roles
  2. Create role
  3. Define DAX filter

Example:

// RLS Filter: Users see only their region
[Region] = USERPRINCIPALNAME()

Power BI Architecture

Architecture Components

  1. Data Sources: SQL, Excel, APIs, etc.
  2. Power BI Desktop: Authoring tool
  3. Power BI Gateway: On-premises data gateway
  4. Power BI Service: Cloud platform
  5. Power BI Mobile: Mobile apps

Data Flow

Data SourcesPower QueryETLData ModelDAXVisualizationsPower BI ServiceUsersweb, mobile

Deployment Options

1. Cloud-Only

2. Hybrid

3. On-Premises

Power BI AI Integration

AI-Powered Features

1. Quick Insights

Automatically find insights in data

How to Use:

  1. Select a visual
  2. Click "Get Insights"
  3. Power BI suggests insights

2. Q&A (Natural Language)

Ask questions in plain English

Example Questions:

3. Key Influencers

Identify factors that influence metrics

How to Use:

  1. Visualizations → Key Influencers
  2. Select metric to analyze
  3. Select fields to analyze

Example:

4. Decomposition Tree

Break down metrics to find root causes

How to Use:

  1. Visualizations → Decomposition Tree
  2. Select metric
  3. Drill down by dimensions

5. Anomaly Detection

Automatically detect outliers

How to Use:

  1. Select time series visual
  2. Enable anomaly detection
  3. Power BI highlights anomalies

AI Visuals

Key Influencers: Identify factors that influence metrics.

Steps:

  1. Visualizations → Key Influencers
  2. Select metric to analyze
  3. Select fields to analyze
  4. Review influencers

Example:

Smart Narratives: AI-generated text insights.

Steps:

  1. Visualizations → Smart Narrative
  2. Select measures
  3. AI generates insights
  4. Customize text

Decomposition Tree: Break down metrics to find root causes.

Steps:

  1. Visualizations → Decomposition Tree
  2. Select metric
  3. Drill down by dimensions
  4. Identify contributors

Q&A Visual: Natural language querying.

Steps:

  1. Visualizations → Q&A
  2. Type question in plain English
  3. Power BI creates visual
  4. Refine question

Example Questions:

Integration with Copilot & AI Formula Builder: AI-powered assistance.

Copilot Features:

AI Formula Builder:

Performance & Advanced Analytics

Use the Analyze Feature: Quick insights on visuals.

Steps:

  1. Select visual
  2. Analytics pane → Analyze
  3. Choose analysis type
  4. Review insights

Group, Bin, and Cluster Data: Organize data for analysis.

Grouping:

Binning:

Clustering:

Identify Patterns and Trends:

Integrate Power BI with Python & R: Use data science languages.

Python Integration:

  1. Get Data → Python script
  2. Write Python code
  3. Use pandas, matplotlib, etc.
  4. Return DataFrame

Example:

import pandas as pd
import matplotlib.pyplot as plt

# Access Power BI data
df = dataset

# Perform analysis
result = df.groupby('Category')['Sales'].sum()

# Return to Power BI
result

R Integration:

  1. Get Data → R script
  2. Write R code
  3. Use R packages
  4. Return data frame

Use What-If Parameters & Scenario Simulation: Model different scenarios.

Steps:

  1. Modeling → New Parameter
  2. Define parameter range
  3. Create measure using parameter
  4. Use slicer to adjust

Example:

// Parameter: DiscountRate (0% to 50%)
Adjusted Sales = 
SUM(Sales[Amount]) * (1 - DiscountRate[DiscountRate Value])

Perform Forecasting & Trendline Analysis: Predict future values.

Forecasting:

  1. Select time series visual
  2. Analytics pane → Forecast
  3. Configure settings
  4. Review forecast

Trendline:

  1. Select visual
  2. Analytics pane → Trendline
  3. Choose type (Linear, Polynomial, etc.)
  4. Display equation/R²

Azure AI Integration

1. Azure Machine Learning

Use ML models in Power BI

How to Use:

  1. Get Data → Azure Machine Learning
  2. Select model
  3. Use in reports

2. Cognitive Services

Use AI services for text/image analysis

Example:

Best Practices

Design Best Practices

  1. Use Consistent Colors: Create theme
  2. Limit Visuals per Page: 3-5 visuals max
  3. Use Appropriate Chart Types: Match data to visualization
  4. Add Context: Titles, descriptions, tooltips
  5. Optimize Performance: Limit data, use aggregations

DAX Best Practices

  1. Use Measures, Not Calculated Columns: For aggregations
  2. Avoid Nested IFs: Use SWITCH instead
  3. Use Variables: Improve readability
  4. Optimize CALCULATE: Minimize filter modifications
  5. Test Performance: Use DAX Studio

Data Modeling Best Practices

  1. Star Schema: Fact and dimension tables
  2. Proper Relationships: One-to-many preferred
  3. Hide Unnecessary Columns: Clean field list
  4. Use Hierarchies: Enable drill-down
  5. Optimize Data Types: Use appropriate types

Security Best Practices

  1. Row-Level Security: Implement RLS
  2. Limit Sharing: Only share with authorized users
  3. Avoid Publish to Web: For sensitive data
  4. Use Workspaces: Organize by team/project
  5. Audit Logs: Monitor access

Resources

Official Documentation

Free Courses

Communities

Books

Try next: Build one dashboard with three visuals and one DAX measure. Show it to someone who will use it.