Microsoft Excel Power Query for Business Intelligence Training Course

Course overview

Business teams often rely on manually refreshed spreadsheets assembled from exports, shared folders, finance systems and operational applications. The result is slow reporting cycles, inconsistent calculations and a high risk of errors when source files change. This five-day Microsoft Excel Power Query for Business Intelligence Training Course equips analysts to replace repetitive copy-and-paste preparation with documented, refreshable data pipelines inside Excel. Participants learn how to turn raw, differently structured business data into trusted tables for management reporting, operational analysis and recurring KPI packs.

The course covers the complete Power Query workflow: connecting to workbooks, CSV files, folders, databases and web sources; profiling data quality; cleaning and standardising fields; combining files and tables; shaping datasets through joins, appends, pivots and unpivots; and building reusable transformation logic with M. Participants learn to design parameter-driven queries, manage refresh dependencies, handle errors and create a controlled Excel data model using Power Pivot relationships and DAX measures. The emphasis is on methods that make reports traceable, maintainable and fit for business intelligence use.

Instructor-led demonstrations are followed by guided builds and realistic case exercises using sales, budget, customer and operational datasets. Participants progressively construct an automated reporting solution that ingests monthly source files, applies repeatable transformations and loads a governed model for analysis. They leave with a completed Power Query workbook, a documented query map, reusable M transformation patterns and an action plan for applying the approach to a live reporting process in their own organisation.

This course is suited to experienced Excel users who prepare recurring reports, reconcile data from multiple systems or need to create more reliable self-service business intelligence outputs without depending on manual spreadsheet consolidation.

Course objectives

By the end of this course, participants will be able to:

• Connect Excel Power Query to workbooks, CSV files, folders, SQL Server databases and web-based data sources
• Profile source data to identify nulls, errors, duplicates, outliers and inconsistent data types
• Apply repeatable cleansing transformations using the Power Query interface and M expressions
• Combine monthly files and related tables through folder queries, append operations and merge joins
• Reshape reporting datasets using pivot, unpivot, grouping, conditional columns and custom columns
• Build parameter-driven queries and reusable functions for controlled, scalable data refreshes
• Load curated query outputs into a Power Pivot data model with relationships and DAX measures
• Produce a documented automated reporting workbook with query dependencies, refresh instructions and exception handling

Benefits of attending

Target competencies

Who should attend

• Business Analysts — who consolidate data from multiple systems for recurring management reports
• Financial Analysts — who refresh budget, forecast and actuals packs from changing monthly source files
• Data Analysts — who need a governed Excel-based method for preparing analysis-ready datasets
• Management Information Analysts — who maintain KPI reporting and need to reduce manual data preparation
• Operations Managers — who need reliable operational dashboards built from exports and departmental files
• Excel Power Users — who already use formulas and PivotTables but need automated data transformation skills

Requirements and prerequisites

Participants should be confident Excel users and able to work with tables, worksheets, formulas, filters, sorting and PivotTables. Experience importing CSV files or working with business-system exports is useful, as is familiarity with basic relational concepts such as rows, columns, keys and lookup fields. Attendees should bring access to Excel for Microsoft 365 or Excel 2019/2021 for Windows; the full Power Query experience is required. No prior knowledge of M language, SQL, Power Pivot, DAX, Power BI or programming is required. Participants do not need to be database administrators, but should understand the reporting processes they want to improve.

Training methodology

Each day combines focused instructor-led explanation with live Power Query builds in Excel. Participants work through progressively more complex datasets, including inconsistent monthly files, customer and sales tables, budget extracts and operational logs. Exercises require attendees to inspect query steps, diagnose refresh failures, choose joins and reshape data for reporting rather than merely follow clicks. Small-group review sessions compare alternative transformation designs and their maintenance implications. On the final day, participants complete an end-to-end reporting workbook and create a practical adoption plan for one of their own recurring data processes.

Course outline

Day 1: Power Query foundations and data quality

• Power Query architecture within Excel and the Get & Transform Data interface
• Query lifecycle from source connection to worksheet, Data Model and connection-only load
• Importing Excel tables, CSV files, text files and structured folder sources
• Data type detection, locale settings and type conversion controls
• Column profiling, column quality and column distribution analysis
• Managing null values, errors, duplicates and inconsistent text values
• Applied Steps, query settings and source lineage documentation

Workshop: Participants profile a flawed sales export and produce a documented cleaned query that resolves data types, missing values and duplicate records.

Day 2: Transforming and reshaping business data

• Filtering rows and columns without compromising refresh logic
• Splitting, extracting, replacing and standardising text fields
• Date, time and fiscal-period transformations
• Conditional columns, index columns and custom columns
• Grouping and aggregation for operational and financial summaries
• Pivoting and unpivoting cross-tab reports into analysis-ready tables
• Reference queries versus duplicate queries for reusable transformation layers

Workshop: Participants convert a manually maintained budget cross-tab into a normalised fact table with calculated reporting periods and validation columns.

Day 3: Combining sources and automating file ingestion

• Append queries for combining compatible datasets and historical files
• Merge queries and join types including left outer, inner, full outer and anti joins
• Matching transaction data to customer, product and cost-centre lookup tables
• Folder connectors for automated monthly file consolidation
• Sample file transformations and the Combine Files pattern
• Data source settings, privacy levels and credential management
• Refresh dependencies and query load order troubleshooting

Workshop: Participants build a folder-based monthly sales consolidation process and produce an exception query for unmatched customers and invalid product codes.

Day 4: M language, parameters and robust query design

• Reading and editing M code in the Advanced Editor
• M syntax, let expressions, step names, lists, records and tables
• Parameters for file paths, reporting dates and environment-specific sources
• Creating reusable custom functions for repeated transformations
• Error handling with try, otherwise and error-value replacement
• Query folding concepts and performance implications for database sources
• Naming conventions, query groups and maintainable query documentation

Workshop: Participants parameterise a reporting workbook and create a reusable M function that standardises imported departmental extracts while handling malformed rows.

Day 5: Excel data modelling and business intelligence delivery

• Loading curated queries to the Excel Data Model
• Fact tables, dimension tables, grain and star-schema design
• Creating Power Pivot relationships and managing key integrity
• DAX measures for totals, ratios, variances and year-to-date analysis
• PivotTables, PivotCharts and slicers connected to the Data Model
• Refresh testing, reconciliation checks and reporting control procedures
• Deployment planning for shared workbooks, source ownership and user refresh guidance

Workshop: Participants complete an end-to-end management reporting workbook with a Power Query pipeline, Power Pivot model, DAX measures, KPI PivotTable and refresh runbook.

Tools & standards covered

Microsoft Excel for Microsoft 365, Microsoft Power Query, Microsoft Power Pivot, Microsoft Power BI Desktop

A typical training day

Live online deliveries follow the same structure in the East Africa Time zone, with shorter screen blocks and longer breaks.

What the fee includes

How you can take this course

Certification

Participants who complete the full five days receive the Skillset Development Certificate of Completion, stating the course title, course code, dates and delivery format — suitable for professional-development records and employer reimbursement.

Frequently asked questions