Master Power BI From Scratch

Learn Power BI fundamentals, data sources, data transformation, data modeling, DAX, visualizations, reports, and dashboards through structured practical lessons.

COURSE SYLLABUS
1. Power BI Introduction
Introduction to Power BI Power BI Desktop and Service Power BI Workflow Power BI Terminology Power BI Workspace Overview
2. Power BI Installation and Interface
Power BI Installation Power BI Desktop Interface Report, Data and Model Views Power BI File Management Power BI Settings and Options
3. Power BI Data Sources
Excel and CSV Data Sources Text and Folder Data Sources Database Data Sources Web and Online Data Sources Data Source Credentials and Refresh
4. Power BI Data Transformation
Power Query Introduction Data Types and Column Operations Merge and Append Queries Split, Group and Pivot Columns Power Query Applied Steps
5. Power BI Data Cleaning
Missing and Null Values Remove Duplicates and Errors Text and Date Cleaning Replace and Fill Values Data Quality Validation
6. Power BI Data Modeling
Data Modeling Fundamentals Create Table Relationships Cardinality and Filter Direction Star Schema and Dimension Tables Date Tables and Model Optimization
7. DAX
DAX Introduction and Syntax DAX Aggregation Functions DAX Logical and Conditional Functions DAX Filter and Iterator Functions DAX Date and Time Intelligence
8. Power BI Calculations
Calculated Columns Measures and Implicit Measures Row Context and Filter Context Quick Measures and KPI Time-Based Business Calculations
9. Power BI Visualizations
Basic Power BI Charts Pie, Donut and Treemap Charts Cards, Tables and Matrices Maps and Geographic Visuals Advanced and Custom Visuals
10. Power BI Filters and Slicers
Filter Types and Filter Pane Slicers and Sync Slicers Drill Down and Drill Through Cross-Filtering and Highlighting Bookmarks and Buttons
11. Power BI Reports and Dashboards
Report Page Layout and Design Multi-Page Reports Dashboard Creation and Tiles Executive and Business Reports Report Usability and Accessibility
12. Power BI Formatting and Interactivity
Themes and Color Formatting Conditional Formatting Tooltips and Report Page Tooltips Visual Interactions and Edit Interactions Mobile Layout and Responsive Reports
13. Power BI Publishing and Sharing
Publish Reports to Service Workspaces and Apps Scheduled Refresh and Gateway Sharing and Permissions Row-Level Security and Deployment
14. Power BI Projects and Assessment
Sales Dashboard Project Student Performance Project Data Cleaning and Modeling Assignment DAX and Visualization Assessment Final Power BI Project and Assessment

Introduction to Power Query

Learn how Power Query helps you connect to data, clean inconsistent values, transform columns, filter rows, and prepare reliable datasets before building Power BI reports.

1. What Is Power Query?

Power Query is a data connection and transformation technology used in Power BI and other Microsoft products. It helps users retrieve data from different sources and prepare it for analysis.

What Can You Do with Power Query?

  • Connect to Excel, CSV, databases, folders, and supported web sources.
  • Remove unnecessary rows and columns.
  • Rename columns and change data types.
  • Replace values and handle missing data.
  • Split columns and combine datasets.
  • Merge or append queries.
  • Save transformation steps for later refreshes.
Excel / CSV Database Web Data Power Query Clean and transform Repeatable steps Prepared Data Model and report Ready for analysis

Visual 1: Power Query combines data connection and preparation into a repeatable workflow.

2. Opening Power Query Editor

Power Query Editor provides the workspace where you can inspect source data and apply transformation steps before loading it into the Power BI data model.

Steps to Open Power Query Editor

  1. Open Power BI Desktop.
  2. Connect to a data source using Home → Get Data.
  3. Select the required file, table, or data object.
  4. Choose Transform Data to open Power Query Editor.
  5. Review the preview, query list, ribbon, and Query Settings pane.

Main Areas of Power Query Editor

  • Queries pane: Lists queries available in the current Power Query session.
  • Data preview: Displays a preview of the selected query.
  • Ribbon: Provides transformation and data management commands.
  • Formula bar: Displays the expression for a selected step when enabled.
  • Query Settings: Shows query properties and Applied Steps.
Power Query Editor Queries Sales Products Customers Transform Data Ribbon Product Region Quantity Amount Laptop West 2 50000 Monitor North 3 24000 Query Settings Properties Applied Steps Source Changed Type Filtered Rows Renamed Columns

Visual 2: The editor organizes queries, data preview, transformation commands, and applied steps.

3. Understanding Applied Steps

When you perform a transformation, Power Query usually records it as an applied step. These steps define how the original data is converted into the prepared result.

Example Transformation Sequence

  1. Source: Connect to the original dataset.
  2. Navigation: Select the required worksheet or table.
  3. Promoted Headers: Use the appropriate row as column headings.
  4. Changed Type: Assign appropriate data types.
  5. Removed Columns: Remove unnecessary fields.
  6. Filtered Rows: Keep the required records.
Source ↓ Navigation ↓ Promoted Headers ↓ Changed Type ↓ Removed Columns ↓ Filtered Rows ↓ Prepared Table

You can select a step to inspect the data at that point. Some steps can be edited or removed, but removing a step may affect later steps that depend on it.

Best practice: Use clear query and column names, and review the result after important transformations.

4. Basic Data Cleaning Operations

Data cleaning improves consistency and helps prevent errors in calculations and visualizations. Choose transformations according to the actual data and business requirements.

Operation Purpose Example
Remove Columns Delete fields that are not required. Remove an unused internal note.
Filter Rows Keep records that meet selected conditions. Keep sales from the current year.
Replace Values Standardize known inconsistent values. Replace an approved legacy category label.
Trim Text Remove leading and trailing spaces. Convert " West " to "West".
Change Data Type Interpret values correctly. Convert a text date into a date value.
Remove Duplicates Remove repeated rows or values based on selected columns. Keep one record per unique customer ID when appropriate.

5. Example: Transforming Sales Data

Consider a sales dataset containing inconsistent region names, extra spaces, and amounts stored as text. The following example illustrates how transformation improves the data.

Before Transformation

Order ID Region Amount
101 West 5000
102 West 3000
103 North 2500

After Transformation

Order ID Region Amount
101 West 5000
102 West 3000
103 North 2500

Trimming whitespace makes the Region values consistent. The Amount column should also be assigned a numeric data type before performing numeric calculations.

Before Cleaning "West" " West " Trim After Cleaning West West Consistent values improve grouping and filtering.

Visual 3: A simple text transformation can prevent duplicate-looking categories in reports.

6. Understanding Data Types

Data types tell Power Query how to interpret values. Correct types help prevent calculation errors and support appropriate filtering, sorting, and modeling.

Data Type Example Common Use
Text Customer A Names and categories
Whole Number 25 Counts and integer quantities
Decimal Number 25.75 Measurements and decimal values
Fixed Decimal Number 125.50 Suitable fixed-precision numeric scenarios
Date 2026-05-12 Calendar dates
Date/Time 2026-05-12 10:30 Date and time values
True/False True Logical conditions
Important: Identifiers such as customer codes or product IDs may need to remain text even when they contain only digits. Choose the type based on how the field is used.

7. Apply Changes and Load Data

After preparing the data, use Close & Apply in Power BI Desktop to apply the query changes and load the results into the data model, subject to the current connection and model configuration.

Before You Apply Changes

  • Check column names for clarity.
  • Verify the data types.
  • Review null values and errors.
  • Confirm that required rows are present.
  • Check whether any transformation removed needed data.
  • Rename queries to meaningful names.
Source Data Raw values Transform Clean and prepare Apply Load changes Data Model Build reports Review the final result before using it in report visuals.

Visual 4: Prepared query results can be applied to the Power BI data model.

8. Practical Exercise

Exercise: Clean a Sales Dataset

Scenario: A sales team receives a spreadsheet with inconsistent region names and incorrectly typed amounts.

  1. Import the spreadsheet into Power BI Desktop.
  2. Select Transform Data.
  3. Rename the query to SalesData.
  4. Remove any unnecessary columns.
  5. Trim the Region column to remove extra spaces.
  6. Set Amount to a suitable numeric type.
  7. Review and correct any data conversion errors.
  8. Inspect the Applied Steps list.
  9. Select Close & Apply.
  10. Create a simple table visual to verify the loaded data.

Expected outcome: A clean and consistently typed sales dataset ready for modeling and reporting.

9. Knowledge Check

Question 1: What is the primary purpose of Power Query?

A. Design slide presentations
B. Connect to and transform data
C. Create operating system accounts
D. Format documents

Question 2: What does the Applied Steps list show?

A. The sequence of query transformations
B. Only report colors
C. Only published workspaces
D. A list of users

Question 3: Which operation removes leading and trailing spaces from text?

A. Merge
B. Pivot
C. Trim
D. Append

Question 4: What does Close & Apply generally do in Power BI Desktop?

A. Delete all queries
B. Apply query changes and load results according to the model configuration
C. Publish the report automatically
D. Remove data source credentials

View Answers
  1. B — Connect to and transform data.
  2. A — The sequence of query transformations.
  3. C — Trim.
  4. B — Apply query changes and load results according to the model configuration.

Summary

In this lesson, you learned what Power Query is, how to open Power Query Editor, and how to inspect queries, data previews, and Applied Steps.

You also practiced basic data cleaning, reviewed common data types, and learned how to apply transformations before using the prepared data in the Power BI model.