Model Online / On-Site
Duration
Modules 6 modules

Course Objective & Scope

Copying the same files every month, fixing columns and rebuilding formulas consumes much of an analyst's time. The programme practises importing from Excel, CSV, folders, SQL databases and SharePoint, data types, merge and append, pivot/unpivot and error handling through the Power Query interface.

The second part covers the M language in the Advanced Editor, parameters, custom functions and the concept of query folding. Participants convert a messy monthly reporting process into a reusable query chain that runs with a single refresh.

Who Should Attend?

  • Excel users who prepare regular reports
  • Analysts doing data preparation in Power BI
  • Finance, accounting and operations specialists consolidating data
  • Prerequisite: knowledge of Excel tables and basic formulas

Key Takeaways

  • Combine data from different sources in a single query flow
  • Prepare data for analysis with unpivot, grouping and conditional columns
  • Automatically consolidate multiple files from a folder
  • Write reusable queries with parameters and custom functions
  • Improve query performance by preserving query folding

Curriculum

Connecting to Data Sources
  • Excel, CSV, folder, SQL and SharePoint connections
  • Privacy levels and credentials
Basic Transformations and Data Cleaning
  • Data types, splitting, fill and replace
  • Handling errors and null values
Combining and Reshaping Data
  • Merge join kinds and append
  • Pivot, unpivot and grouping
M Language and the Advanced Editor
  • let/in structure, steps, records and lists
  • Custom column formulas
Parameters, Functions and Query Folding
  • Parameterised sources and custom functions
  • Checking query folding and performance
Case: Automating a Monthly Report Process
  • Consolidating multiple files from a folder
  • Query documentation and checklist

Other Courses in This Category

  • Microsoft Fabric Fundamentals

    OneLake, Lakehouse, Data Warehouse, Data Factory pipelines, Dataflow Gen2, notebooks and Direct Lake Power BI, giving data and BI teams an end-to-end introduction to Microsoft Fabric components.

    Business Intelligence & Analytics Corporate
    Live Course
    6 modules
    View
  • Migrating from Power BI to Microsoft Fabric

    Moving Power BI Premium/Pro environments to Fabric capacity, converting dataflows to Gen2 and sources to Lakehouse and Direct Lake, so BI teams produce a risk-controlled migration roadmap.

    Business Intelligence & Analytics Corporate
    Live Course
    6 modules
    View
  • Data Analysis with Excel

    PivotTables, XLOOKUP, dynamic array functions (FILTER, UNIQUE, SORT), conditional aggregation, What-If analysis and Power Pivot, so business users perform fast and reliable analysis in Excel.

    Business Intelligence & Analytics Corporate
    Live Course
    6 modules
    View
  • Python in Excel: Automating Analysis with pandas

    Python in Excel (=PY), pandas for cleaning and transforming data, openpyxl report automation and matplotlib charts, helping Excel users scale repetitive analysis with Python.

    Business Intelligence & Analytics Corporate
    Live Course
    6 modules
    View
  • SQL and Power BI for Business Analysts

    Querying databases with SELECT, JOIN, GROUP BY, CTEs and window functions, then modelling and reporting in Power BI, so business analysts produce their own analyses without depending on IT.

    Business Intelligence & Analytics Corporate
    Live Course
    6 modules
    View
  • Tableau for Data Analysis and Dashboards

    Data connections, dimensions and measures, calculated fields, LOD expressions, parameters and interactive dashboard design in Tableau Desktop, plus publishing to Tableau Cloud for visual analysis.

    Business Intelligence & Analytics Corporate
    Live Course
    6 modules
    View

Get in touch