Course Overview

Power Query in Power BI : This 2-day hands-on course is designed to help participants build strong practical skills in Power Query within Power BI Desktop. Participants will learn how to clean, transform, combine, and prepare data from various sources before loading it into Power BI for reporting and dashboard development.

The course focuses on essential Power Query transformation techniques including text, date, and number column operations, appending and merging data, using column from examples, applying conditional columns, and performing common data preparation tasks such as grouping, unpivoting, replacing values, managing errors, and setting data types.

By the end of the course, participants will be able to confidently use Power Query Editor to transform raw data into structured, analysis-ready datasets.

Course Objectives

By the end of this Power Query in Power BI 2-day course, participants will be able to:

1. Understand the role of Power Query in Power BI Desktop.
2. Clean and transform text, date, and number columns using Power Query features.
3. Perform common text transformations such as merge, split, trim, change case, extract, and add prefix or suffix.
4. Work with date columns to extract year, quarter, month, day, date differences, age, and other date-related values.
5. Apply number transformations such as arithmetic operations, percentage, modulo, rounding, even/odd checking, and sign identification.
6. Append multiple files and tables from different sources such as CSV, Excel sheets, folders, and mixed data sources.
7. Merge tables using single or multiple matching columns.
8. Use Column from Examples to generate new columns based on sample patterns.
9. Create conditional columns based on one or more conditions.
10. Perform miscellaneous Power Query transformations including fill down/up, grouping, transpose, unpivot, replace values/errors, keep/remove rows, and manage columns.
11. Prepare clean and reliable datasets for Power BI reports and dashboards.

Course Content

Day 1: Power Query Fundamentals and Column Transformations

Module 1: Introduction to Power Query in Power BI Desktop

Topics Covered:

• 1.1 Overview of Power Query and Power BI Desktop
• 1.2 Understanding the Power Query Editor interface
• 1.3 Query steps and Applied Steps pane
• 1.4 Data loading process in Power BI
• 1.5 Basic data cleaning workflow

Practical Activity:

• Import sample data into Power BI Desktop
• Explore Power Query Editor
• Apply simple transformations and review Applied Steps

Module 2: Transforming Text Columns

Participants will learn how to clean, format, combine, and extract text values.

Topics Covered:

• 2.1 Merge
  • Combine two or more text columns into one column.

• 2.2 Split and Trim
  • Split text by delimiter and remove unnecessary spaces.

• 2.3 Upper, Lower and Proper
  • Convert text case for standardization.

• 2.4 Add Suffix and Prefix
  • Add fixed text before or after existing values.

• 2.5 Left, Right and Mid
  • Extract specific characters from the beginning, end, or middle of a text value.

• 2.6 Extract Text with Delimiter
  • Extract text before, after, or between delimiters.

Practical Activity:

• Clean customer names and product codes
• Split full names into first and last name
• Extract department codes from employee IDs
• Standardize text formatting

Module 3: Transforming Date Columns

Participants will learn how to extract and calculate date-related information.

Topics Covered:

• 3.1 Year, Quarter, Month, Day
  • Extract date components for reporting and analysis.

• 3.2 Difference Between Dates, Earliest and Latest
  • Calculate duration between dates and identify earliest/latest dates.

• 3.3 Name of Day and Name of Month
  • Extract day and month names for business reporting.

• 3.4 Day of Week / Month / Year and Week of Month / Year
  • Generate calendar-based values for analysis.

• 3.5 Extract Date from Date and Time
  • Separate date values from date-time fields.

• 3.6 Calculate Age
  • Calculate age using date of birth or date difference logic.

• 3.7 Which Day of Year, Quarter, Month Your Date of Birth
  • Determine the day position within a year, quarter, or month.

Practical Activity:

• Create calendar-related columns from transaction dates
• Calculate customer age from date of birth
• Extract day name and month name from sales dates
• Calculate number of days between order date and delivery date

Module 4: Transforming Number Columns

Participants will learn how to perform arithmetic and numerical transformations.

Topics Covered:

• 4.1 Add, Subtract, Divide and Multiply
  • Create calculated columns using basic arithmetic operations.

• 4.2 Percentage, Percent Of, Modulo
  • Calculate percentage values and remainder values.

• 4.3 Rounding the Numbers
  • Round numbers up, down, or to a specific decimal place.

• 4.4 IsEven, IsOdd and Sign
  • Identify even/odd numbers and determine positive or negative values.

Practical Activity:

• Calculate sales amount, discount amount, and net sales
• Calculate percentage contribution by product
• Round currency values
• Identify positive and negative transaction values

Day 1 Summary and Review

Activities:

• Recap of text, date, and number transformations
• Guided practice exercise
• Q&A session

Module 8: Conditional Columns

Participants will learn how to create columns based on logical conditions.

Topics Covered:

• 8.1 One Column
  • Create a conditional column based on values in one column.

• 8.2 Two Columns
  • Create conditions using two columns.

• 8.3 Comparing Two Column Values
  • Compare values such as target vs actual, start date vs end date, or cost vs budget.

• 8.4 Dates
  • Create conditions based on date values.

Practical Activity:

• Categorize sales performance as High, Medium, or Low
• Compare planned and actual delivery dates
• Flag overdue transactions
• Create age group or membership category

Module 9: Miscellaneous Power Query Transformations

Participants will learn additional important Power Query tools used in real-world data preparation.

Topics Covered:

• 9.1 Fill Down and Fill Up
  • Fill missing values using values above or below.

• 9.2 Grouping
  • Group records and calculate summary values.

• 9.3 Transpose
  • Convert rows into columns and columns into rows.

• 9.4 Unpivot
  • Convert wide data into normalized tabular format.

• 9.5 Data Types
  • Set and manage correct data types for each column.

• 9.6 Replace Errors and Values
  • Replace incorrect values and handle errors.

• 9.7 Keep Rows and Remove Rows
  • Filter rows by position, condition, blank values, errors, or duplicates.

• 9.8 Add, Remove, Go To Columns
  • Manage columns efficiently in Power Query Editor.

Practical Activity:

• Clean messy Excel reports
• Unpivot monthly sales columns
• Replace errors and missing values
• Group sales by region and product category
• Remove unnecessary rows and columns