Faysal Aziz
← All courses

Training

Course Outline for Power Query for Analytics

Course Outline for Power Query for Analytics

Power Query is now an integrated part of Microsoft Excel. It provides advanced transformations and management tools that would otherwise be very cumbersome to perform using traditional methodologies in Excel.

This course will equip the learner to use these advanced features and tools. This is essentially a more advanced Excel training in effect and assumes that the learner is comfortable using MS Excel.

This training will use real project files sourced from Google’s Kaggle as well as have assessments in every major module to ensure that the learner is at par with the training pace.

Learning Outcome

By the end of this training, the learner should be able to:

  • Combine and consolidate large data from different sources like Excel, text files, the web, online services, databases, even your Outlook (if need be).
  • Convert downloads from your ERP system into information you can use.
  • Replace complex Excel formulas with a click of a button.
  • Turn complex data transformations that took forever to do (such as unpivoting data), to just a few clicks.
  • Import, transform and clean large data from different sources (Excel, csv, web, sharepoint etc.)
  • Combine data from multiple Excel workbooks into a single Table (or Pivot Table)
  • Consolidate data from all files in a folder (and make exceptions as you need)
  • Understand and write your own Custom Power Query M functions to do tasks you can't easily do with the interface
  • Use Excel's Data Model and Power Pivot to Create Relationships between your data
  • Understand when to use Excel Power Query and when to Load data to Excel's Data Model
  • Create an interactive Excel Dashboard with Power Query, Data Model, Pivot Tables and slicers

Prerequisites

  • Comfortable using Excel with:
    • xlsx
    • csv
    • txt
  • Column based formula creation
  • Pivoting
  • Filing system
  • Character manipulation
  • Joins and types
  • Data types

Disclaimer

The timing provided is an approximation and is no way to be assumed as absolute. The actual timing required is entirely dependent on the trainees and any delays caused may require omission of modules or addition of time.

Course Outline

  1. The Basics: (Approx 2 hours 30 minutes)
    1. Analyze Large Data Quickly.
    2. Power Query Overview: Import Large Data from Another File
    3. Power Query Editor & Basic Transformation
    4. Quick Insights on Data Quality & Distribution
    5. Formula Bar, Applied Steps & M Code
    6. Close & Load Destinations
    7. Refresh Data & PQ Refresh Options
    8. Import CSV File & Extract Text Based on Pattern
    9. Merge Data with Another File (Pivot Table from Multiple Files)
    10. Old School Method: Comparative analysis?
    11. ASSESSMENT
  2. Intermediate Feature Explorations (Approx 1 hours 30 minutes)
    1. Feature Name Change in Office 365 in 2021
    2. Uploading Data From Excel
    3. The Hidden Table Method
    4. Handling Changes to Source
    5. Data Types in Power Query
    6. Data Types vs. Formatting & Null Values
    7. Power Query Navigation Shortcuts
    8. Finding & Correcting Errors in Data
    9. More Data Views
    10. Query Dependencies
    11. Delete, Manage, Copy Queries & Backup Results
    12. ASSESSMENT
  3. Transformations (Approx 3 hours)
    1. Text Transformations (Format, Extract & more)
    2. Merging Columns & What to Watch Out For
    3. Fill & Replace Values to Create Proper Datasets
    4. Sort Data including Multiple Levels
    5. Remove Duplicates including Multiple Columns
    6. Number Transformations & What to Watch Out For
    7. Working with Filter (AND & OR Conditions)
    8. ASSESSMENT
    9. Challenges with Filter
    10. Change Type & Remove Columns Trap
    11. Column From Examples - Extract Patterns Quickly
    12. Allocate Data to Groups or Buckets
    13. Conditional Columns in Power Query
    14. Aggregating (Grouping) Data on Multiple Levels
    15. Group By for All RowsUnpivot Columns - Basics
    16. Unpivot & How to Overcome Common Errors
    17. Pivot Columns - Basics
    18. Problem with Split by Delimiter
    19. Split Column by Rows instead of Columns
    20. ASSESSMENT
  4. Advanced Features Exploration (Approx 2 hours)
    1. Date Transformations (Extract Age, Weekday etc.)
    2. Creating Dates from Text or Columns
    3. Time Transformations (Calculating Hours worked)
    4. Date & Number Errors When Importing Data
    5. ASSESSMENT
    6. Important Basic Power Query M Logic
    7. Logic behind Custom Columns
    8. Introduction to "Add Custom Column"
    9. Custom Columns Type Compatibility & Intrinsic Functions
    10. Skipping Steps in Power Query
    11. Adjusting FILTER & Conditional Columns to Reference a Dynamic Variable
    12. Drill-Down in Power Query
    13. Custom Formulas for Template Creation
    14. ASSESSMENT
  5. Variable Data Sources (Approx 30 minutes)
    1. Connecting to different Sources
    2. Import Data from a Website
    3. Automatically Connect to Files on Websites
    4. Get Google Sheet Data with Power Query
    5. Connect to Outlook Online (Microsoft Exchange)
    6. Connect to SharePoint or OneDrive for Business
    7. How to Change Source from Local to SharePoint
    8. ASSESSMENT
  6. Conjunction (Approx 2 hour 30 minutes)
    1. Why Append Data? The Difference Between Merge & Append
    2. Combine / Append Data from Multiple Workbooks
    3. Combine All Files in a Folder (with Excel Tables)
    4. Combine All Files in a Folder (Without Excel Tables)
    5. How to Adjust Folder Path from Local to SharePoint Drive
    6. Combine All Sheets in a File (Pivot Table from Multiple Sheets)
    7. Overcome Potential Errors when Combining Sheets
    8. Consolidate Data from Multiple Sheets in the Current Workbook
    9. Overview of Merge Options and Join Kinds
    10. Left Outer Join & Right Outer Join
    11. Merge Based on Multiple Columns
    12. Merging Text Columns
    13. Merge Data to Get Multiple Match Results &
    14. Inner & Full Join in Power Query
    15. Left & Right Anti Join when Merging in Power Query
    16. How to Use Fuzzy Match in Power Query
    17. Fuzzy Match with Transformation Table
    18. ASSESSMENT
  7. Power Pivot (Approx 30 minutes)
    1. When to Load Data to the Data Model
    2. Availability of Power Pivot
    3. Pivot Table from Multiple Excel Tables
    4. Power Pivot Table with Data Model & Power Query
    5. Create a Calendar Table in Power Pivot
    6. Pivot Slicers & TimeLine with Power Pivot & Power Query
    7. ASSESSMENT
  8. Analytics (Approx 2 hours)
    1. Messy Data from Multiple Rows to One Row
    2. Search and Replace Bulk Values
    3. Calculate Value Difference to Previous Row
    4. Approximate Match Lookup with Merge
    5. ASSESSMENT
    6. Assign Unique Number to Group
    7. Advanced Unpivot Techniques
    8. Advanced Pivot Techniques
    9. Incremental Data Load & Self Referencing Query
    10. Import Master data from External Workbook with Power Query
    11. Import Data from Text File with Power Query
    12. Create the Data Model & Define Relationships in Power Pivot
    13. Create Logic for Latest and Previous Month in Power Query
    14. Setup Calculations with Pivot Tables for Latest Month
    15. Link Excel Shapes to Data & Linked Picture Trick
    16. Sorted Excel Pivot Table
    17. Linked Table for Sales by Product Category
    18. Excel Pivot Chart for Monthly Sales
    19. Pivot Slicer Connected to Multiple Pivot Tables
    20. Finalize the Excel Dashboard
    21. ASSESSMENT

Practical, connected learning

My wider training approach brings hands-on implementation and systems thinking together, connecting technology with real operational needs.

Explore more courses

View all courses →