Course  Overview for Mastering Power Query in Power BI Desktop for Data Transformation

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 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

Day 2: Combining Data, Conditional Logic and Advanced Transformations

Module 5: Appending Files and Tables

Participants will learn how to combine rows from multiple tables or files.

Topics Covered:

• 5.1 Append Multiple CSV Files from a Folder
  Combine several CSV files stored in a folder.
• 5.2 Append Multiple Excel Tables / Sheets from Single File
  Combine multiple tables or worksheets from one Excel workbook.
• 5.3 Append Excel Tables with Different Number of Columns
  Handle append scenarios where tables have different structures.
• 5.4 Append Multiple Excel Files from a Folder
  Combine similar Excel files from a folder.
• 5.5 Append Different Data Source Files
  Append data from different sources such as CSV, Excel, and other file types.

Practical Activity:

• Append monthly sales files from a folder
• Combine multiple Excel sheets into one table
• Resolve missing columns during append process

Module 6: Merging Files and Tables

Participants will learn how to join related tables using matching columns.

Topics Covered:

• 6.1 Merge Tables / Sheets
  Merge two related tables using a common key.
• 6.2 Merge Data from Multiple Excel Files / Workbooks
  Combine related data from separate Excel workbooks.
• 6.3 Merge Data from Different Data Sources
  Merge data from Excel, CSV, database, or other sources.
• 6.4 Merge Data Having Multiple Matching Columns / Multiple Criteria
  Perform joins using more than one matching column.

Practical Activity:

• Merge sales data with product master data
• Merge employee data with department data
• Use multiple criteria to match records accurately

Module 7: Column from Examples

Participants will learn how to create new columns by providing sample output values.

Topics Covered:

• 7.1 Splitting Text
  Use examples to split names, codes, or descriptions.
• 7.2 Merging Text
  Generate combined text values using sample patterns.
• 7.3 Date Column
  Create date-based outputs from existing date fields.
• 7.4 Alphanumeric Data
  Extract or format values containing both letters and numbers.

Practical Activity:

• Extract product category from product codes
• Generate formatted employee codes
• Create custom date labels using examples

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