Course Introduction
Project teams rely on data from multiple sources to monitor progress, control costs, analyse risks, manage resources, and communicate performance to stakeholders. However, project information is often scattered across Excel workbooks, CSV files, SharePoint lists, databases, financial systems, procurement records, and operational reports. Differences in formats, inconsistent naming conventions, duplicate entries, missing values, and irregular reporting structures can make reliable analysis difficult. The Project Data Transformation Using Power Query Course equips participants with the practical skills required to collect, clean, transform, combine, and prepare project data using Power Query. The programme focuses on building repeatable data preparation processes that improve information quality, reduce manual effort, and support accurate project reporting and decision-making.
Power Query is a data connectivity and transformation technology available through applications such as Microsoft Excel and Power BI. It enables users to connect to supported data sources, apply structured transformation steps, combine datasets, and refresh prepared information when source data changes. Participants will explore the Power Query Editor, the Applied Steps interface, data types, query dependencies, and the principles of repeatable transformation. The course explains how to design queries that can handle recurring project reporting requirements, allowing teams to replace repetitive manual spreadsheet preparation with documented and refreshable data preparation workflows.
A central focus of the programme is improving the quality and consistency of project data before it is used for analysis. Participants will learn how to remove duplicates, manage missing values, standardise text, correct data types, split and merge columns, extract information, replace inconsistent values, and validate records against business rules. The course also examines how to identify common data quality problems in project schedules, cost reports, risk registers, resource allocation tables, procurement records, and progress updates. By applying systematic transformation techniques, participants will be better equipped to produce reliable datasets that can be reused across project reporting cycles.
Project reporting frequently requires information from several files or systems to be brought together into a single analytical dataset. Participants will learn how to append records from multiple reporting periods, merge datasets using matching keys, combine files stored in folders, and manage differences in column names or structures. The programme covers practical techniques for consolidating monthly progress reports, integrating budget and actual expenditure data, combining risk registers, and reconciling project resource information. Participants will also examine query parameters, reusable transformation patterns, and data source considerations that help make recurring data preparation processes more flexible and maintainable.
Beyond basic cleaning and consolidation, the course introduces more advanced Power Query capabilities for handling complex project datasets. Participants will explore conditional columns, custom columns, grouping and aggregation, pivoting and unpivoting, date transformations, error handling, and introductory use of the Power Query M language. The programme explains how transformation order, data types, source structure, and query design affect reliability and performance. Participants will learn to diagnose common refresh problems, manage changing source files, identify transformation errors, and document query logic so that other team members can understand and maintain the resulting data preparation workflows.
By the end of the programme, participants will be able to develop structured Power Query solutions for recurring project data preparation and reporting needs. They will gain practical experience in connecting to data sources, applying transformation steps, consolidating multiple files, validating information, and preparing analysis-ready datasets for Excel, Power BI, and other supported destinations. Through guided exercises and realistic project scenarios, participants will develop reusable data preparation workflows that improve consistency, strengthen reporting confidence, and reduce time spent on manual data manipulation. These capabilities support more efficient project controls, clearer performance analysis, and better-informed management decisions.
Duration
5 days
Who Should Attend
-
Project managers seeking to improve the accuracy, consistency, and efficiency of project performance reporting.
-
Project controls professionals responsible for consolidating schedule updates, cost reports, progress measurements, and project performance data.
-
Project management office analysts who prepare recurring reports and need standardised, refreshable data transformation processes.
-
Business analysts responsible for cleaning, integrating, validating, and preparing datasets for operational and management analysis.
-
Data analysts who use Excel or Power BI and want to develop practical skills in Power Query-based data preparation.
-
Planning and scheduling professionals consolidating programme schedules, activity records, milestone data, and progress updates from multiple sources.
-
Cost engineers and financial analysts who combine budgets, commitments, actual costs, forecasts, and expenditure records.
-
Risk management professionals responsible for standardising risk registers, issue logs, risk ratings, and mitigation action data.
-
Procurement and supply chain specialists who integrate supplier records, purchase orders, delivery information, and procurement performance data.
-
Engineering and construction professionals working with technical registers, inspection records, site reports, and project delivery datasets.
-
Monitoring and evaluation specialists preparing project indicators, outcome measurements, performance records, and periodic reporting datasets.
-
PMO reporting coordinators responsible for maintaining data consistency across multiple projects, departments, and reporting periods.
-
Excel power users seeking to replace repetitive manual data cleaning and consolidation with repeatable query-based processes.
-
Consultants and digital transformation professionals supporting project data integration, reporting improvement, and analytical process standardisation.
Course Objectives
-
Explain the role of Power Query in project data preparation and distinguish its capabilities from spreadsheet formulas, data modelling, and report visualisation tools.
-
Connect to supported project data sources, including Excel workbooks, CSV files, folders, SharePoint, and databases where access and connector support are available.
-
Apply data cleaning techniques to resolve duplicates, missing values, inconsistent text, incorrect data types, formatting problems, and common data quality issues.
-
Design repeatable transformation workflows using the Power Query Editor, Applied Steps, query settings, and clearly structured transformation sequences.
-
Combine project datasets by appending records, merging tables, matching keys, and consolidating files from recurring reporting periods or multiple project teams.
-
Transform project information using conditional columns, custom columns, date and text functions, grouping, aggregation, pivoting, and unpivoting techniques.
-
Apply validation rules and reconciliation checks to identify incomplete records, inconsistent identifiers, invalid values, and discrepancies between project data sources.
-
Use parameters, reusable queries, and introductory Power Query M expressions to make transformation workflows more flexible and easier to maintain.
-
Diagnose common data refresh, source path, schema change, transformation, and type conversion problems while improving query reliability and maintainability.
-
Develop a documented Power Query solution that transforms raw project data into an analysis-ready dataset and demonstrates measurable improvements in preparation efficiency and data quality.
Comprehensive Course Outline
Module 1: Introduction to Power Query and Project Data Preparation
-
Understanding Power Query capabilities in Microsoft Excel and Power BI, including data connectivity, transformation, query management, and refreshable data preparation.
-
Examining common project data challenges such as inconsistent reporting formats, duplicate records, fragmented data sources, missing information, and manual consolidation.
-
Navigating the Power Query Editor, Queries pane, Applied Steps, formula bar, data preview, query settings, and available transformation menus.
-
Establishing a structured approach to project data preparation, including source assessment, transformation planning, validation, documentation, and output requirements.
Module 2: Connecting to Project Data Sources
-
Importing project data from Excel workbooks, CSV files, text files, and supported database connections while assessing source structure and data quality.
-
Connecting to files in folders and consolidating recurring project reports where the files follow compatible structures and supported access arrangements.
-
Accessing supported SharePoint data sources and understanding the differences between importing files, lists, tables, and other available content.
-
Managing data source settings, credentials, source paths, privacy levels, and refresh considerations within the capabilities of the chosen host application.
Module 3: Data Cleaning and Standardisation Techniques
-
Removing duplicate records, blank rows, unnecessary columns, and irrelevant fields while preserving information required for project analysis.
-
Standardising text values through trimming, cleaning, case conversion, replacement, splitting, merging, and extraction of relevant identifiers.
-
Correcting data types for dates, numbers, percentages, currencies, text identifiers, and other fields used in project calculations and reporting.
-
Handling null values, errors, inconsistent categories, malformed records, and missing identifiers using appropriate cleaning and validation strategies.
Module 4: Transforming and Restructuring Project Data
-
Creating conditional and custom columns to classify project records, calculate derived values, assign categories, and apply defined business rules.
-
Extracting and transforming dates to support reporting periods, milestone tracking, month-based analysis, and consistent time-related classifications.
-
Pivoting and unpivoting data to convert between wide and long formats for analysis of costs, progress, resources, and performance indicators.
-
Grouping and aggregating records to calculate project totals, category summaries, resource measures, expenditure figures, and other reporting metrics.
Module 5: Combining Project Data with Merge and Append
-
Appending tables with compatible structures to consolidate monthly reports, departmental submissions, project updates, and historical records.
-
Merging datasets using matching keys to integrate project identifiers, cost codes, supplier records, resource information, and schedule details.
-
Selecting appropriate join types, including inner, left outer, right outer, full outer, and anti joins, to support reconciliation and record matching.
-
Resolving mismatched keys, inconsistent column names, duplicate matches, and missing reference records when combining datasets from different sources.
Module 6: Folder-Based Consolidation and Repeatable Reporting
-
Designing folder-based queries that consolidate recurring project reports and reduce manual copying, pasting, and workbook-by-workbook processing.
-
Using sample files and transformation functions to apply consistent cleaning and restructuring steps across compatible files in a folder.
-
Managing differences in file names, reporting dates, sheet names, column structures, and file formats that may affect consolidation reliability.
-
Developing refresh procedures and source file conventions that support repeatable monthly, weekly, or periodic project reporting cycles.
Module 7: Advanced Transformation Logic and Power Query M Fundamentals
-
Understanding the role of the Power Query M language and how transformation steps are represented in query expressions.
-
Applying introductory M functions for text manipulation, date calculations, conditional logic, type conversion, and handling common data preparation requirements.
-
Using parameters and reusable query patterns to manage changing file locations, reporting periods, source settings, and other configurable transformation inputs.
-
Reviewing query dependencies, step ordering, function usage, and transformation logic to improve clarity, reuse, and maintainability.
Module 8: Data Validation, Reconciliation, and Quality Assurance
-
Establishing validation rules for project identifiers, cost codes, dates, budget values, progress percentages, status fields, and other critical reporting attributes.
-
Identifying missing records, duplicate identifiers, unmatched reference values, invalid categories, and discrepancies between source datasets.
-
Performing reconciliation checks between source totals and transformed outputs to verify that important records and values have been preserved.
-
Creating exception-oriented data preparation processes that flag problematic records for review rather than silently removing information that requires investigation.
Module 9: Query Performance, Refresh Management, and Troubleshooting
-
Understanding factors that influence query performance, including source size, transformation order, data types, query dependencies, and connector capabilities.
-
Applying practical optimisation techniques such as removing unnecessary columns early, filtering irrelevant rows, and avoiding redundant transformation steps.
-
Diagnosing refresh failures caused by changed file paths, renamed columns, missing files, invalid values, credentials, permissions, or source schema changes.
-
Establishing query documentation, refresh checks, error review procedures, and maintenance practices to support dependable recurring project reporting.
Module 10: Capstone Project Data Transformation and Implementation
-
Assessing a realistic project dataset and documenting its source structure, data quality problems, transformation requirements, and intended analytical outputs.
-
Building an end-to-end Power Query solution that imports, cleans, standardises, combines, validates, and prepares project data for reporting.
-
Testing the completed transformation process using reconciliation checks, exception reviews, refresh scenarios, and comparisons against source information.
-
Preparing a reusable data preparation guide that documents query logic, refresh procedures, data quality controls, and opportunities for future improvement.
Training Approach
This course will be delivered by our skilled trainers who have vast knowledge and experience as expert professionals in the fields. The course is taught in English and through a mix of theory, practical activities, group discussion and case studies. Course manuals and additional training materials will be provided to the participants upon completion of the training.
Tailor-Made Course
This course can also be tailor-made to meet organization requirement. For further inquiries, please contact us on: Email: training@upskilldevelopment.com Tel: +254 721 331 808
Training Venue
The training will be held at our Upskill Training Centre. We also offer training for a group (at a discount of 10% to 50%) at requested location all over the world. The Onsite course fee covers the course tuition, training materials, two break refreshments, buffet lunch, airport transfers, Upskill gift package, and guided tour.
Visa application, travel expenses, dinners, accommodation, insurance, and other personal expenses are catered by the participant
Certification
Participants will be issued with Upskill certificate upon completion of this course.
Airport Pickup and Accommodation
Airport pickup and accommodation is arranged upon request. For booking contact our Training Coordinator through Email: training@upskilldevelopment.com, +254 721 331 808
Terms of Payment:
Unless otherwise agreed between the two parties’ payment of the course fee should be done 3 working days before commencement of the training so as to enable us to prepare better.
