Microsoft Excel — Advanced Level

Connect to multiple data sources, then combine, cleanse and transform them into a Data Model — with Copilot drafting the M code for you to paste in and verify.

• Architecture overview
  • –Power Query → Data Model → PivotTable → Dashboard.
  • –Staging, Transform and Load layers.
  • –Naming conventions for queries and steps; documentation standards.
• Connect & combine
  • –From Folder — auto-combine monthly files.
  • –From Excel, CSV, Text, Web and SharePoint.
  • –Combine Files and the helper function it generates — the helper takes each file's binary content, and the file name arrives as the Source.Name column, so you keep it and every row knows which file it came from.
  • –Data source settings, credentials and Privacy Levels — demonstrated, so you know what to do the day a refresh starts asking for permissions.
  • –Parameters and one reusable custom function, invoked with Invoke Custom Function — built together in the lab, so one query serves many files.
• Cleansing & transform
  • –Change data types; Trim, Clean, Split, Extract, Format and Merge columns.
  • –Remove rows, columns, duplicates and errors.
  • –Group By (including All Rows), Fill Down/Up, Replace Values, Index Column.
  • –Pivot and Unpivot to reshape into Fact tables; Transpose, demonstrated.
  • –Conditional Column and Custom Column — if/then/else business rules.
  • –Column from Examples — pattern-based extraction without formulas.
  • –Error handling: Replace/Remove Errors, try ... otherwise, keep bad rows for audit.
  • –Date transforms: Date.StartOfMonth/EndOfMonth, Date.AddMonths, ISO year-month keys, and locale-aware type changes with Change Type > Using Locale. Duration and time functions are on the cheat sheet.
• Merge & append
  • –Append Queries — union of monthly tables.
  • –Merge Queries: Left, Right, Inner, Full and Anti joins.
  • –Fuzzy Matching for imperfect keys.
  • –Anti joins as exception reports — find unmatched or orphaned records.
  • –Choosing the right join: validate row counts before and after.
• Performance & standards
  • –Eliminating redundant steps, and Table.Buffer to force a table into memory — knowing that it stops query folding, which is exactly why you use it sparingly.
  • –Query folding — what it is, and why it only applies when the source can accept a query (SQL Server, ODBC, OData, SharePoint lists). Right-click a step > View Native Query to see the SQL. Folder, Excel and CSV sources never fold, so this is a demonstration rather than a lab on the workshop files.
  • –Load To: Table, Only Create Connection, or Only Create Connection plus Add this data to the Data Model — we deliberately keep one query loaded as a Table so the Copilot exercises have grid data to work on.
  • –Folder structure, file versioning, refresh playbook.
  • –Reference vs Duplicate — staging queries that feed multiple outputs.
  • –Query Dependencies view, demonstrated; Advanced Editor — reading and editing M code, hands-on.
• Copilot & AI-assisted data prep
  • –Copilot in Excel works on the grid, never inside the Power Query editor. Load one staging query to a sheet as a Table so Copilot has something to read, then use it for what it is good at — a formula written from plain language, sorting, filtering, highlighting, a suggested PivotTable or chart, and a first look at the data before you model it.
  • –Prompting patterns: Context → Task → Format. State the source, the target shape and the rules, then iterate and refine with few-shot examples and role prompts.
  • –Copilot drafts the M code: describe the transform in plain language in chat, get M back as text, then paste it into the Advanced Editor and refine it there. Copilot Chat comes with a work or school Microsoft 365 account, so everyone in the room can do this part.
  • –Verify AI output — preview rows, check counts, never trust blind.

Build a Star Schema model and create DAX measures — drafted by Copilot in chat, pasted into Power Pivot and validated by you — for KPIs and Time Intelligence.

• Data modeling (Star Schema)
  • –Fact vs Dimension; Primary and Foreign Keys; cardinality — the grain and the key uniqueness that make a relationship valid.
  • –Active vs Inactive relationships; USERELATIONSHIP.
  • –Date Dimension and Mark as Date Table.
  • –Diagram View; Manage Relationships; one-to-many in Excel, and the bridge-table pattern for many-to-many — Excel has no many-to-many cardinality and no bi-directional filtering, so the bridge pattern is demonstrated rather than built from scratch.
  • –Hierarchies (Year → Month, Category → Product); Hide from Client Tools for key columns; measure organisation and model-size basics — cardinality drives file size.
• DAX fundamentals
  • –Calculated Columns vs Measures.
  • –Row Context vs Filter Context — the foundation of all DAX.
  • –SUM, COUNTROWS, DISTINCTCOUNT, DIVIDE.
  • –RELATED, RELATEDTABLE.
  • –Iterators: SUMX, AVERAGEX, COUNTX — row-by-row logic over a table.
  • –Variables (VAR / RETURN) for readable, debuggable measures.
  • –Implicit vs explicit measures — why explicit always wins.
• CALCULATE & filters
  • –CALCULATE with ALL, ALLEXCEPT and KEEPFILTERS — the filter modifiers that behave the same on every Excel build. REMOVEFILTERS is the newer alias for ALL, and we check it against your build before the course.
  • –Core measures: Total Sales, Cost, Gross Profit, %Margin — hands-on.
  • –Sales LY (DATEADD), YoY% — hands-on.
  • –FILTER as a table argument, and ALLSELECTED for visual totals — demonstrated with a worked example you can replay from the exercise file.
  • –Context Transition — what CALCULATE really does — demonstrated, then practised on one measure.
  • –% of Total, % of Parent; measure branching (reusing measures inside measures).
• Time Intelligence
  • –PREVIOUSDAY/MONTH/YEAR, DATEADD, DATESYTD, TOTALYTD.
  • –Running Total; Moving Average over 3, 6 and 12 months with DATESINPERIOD.
  • –SAMEPERIODLASTYEAR and PARALLELPERIOD; MTD, QTD and YTD side by side — written with TOTALMTD, TOTALQTD and TOTALYTD, or with CALCULATE wrapped around DATESMTD, DATESQTD and DATESYTD, because the DATES… functions return a table of dates rather than a number.
  • –Fiscal years: the year-end-date argument on TOTALYTD and DATESYTD, plus fiscal columns in your own date table — Excel has no global fiscal-year setting.
• KPIs & ranking
  • –Format at the Measure level; Power Pivot KPIs — base measure, target and status thresholds. Trend comes from your own YoY or MoM measure shown as an icon set or a sparkline, because the KPI dialog has no trend component.
  • –Sort by Column, RANKX, Actual vs Target, Variance, % Achievement.
  • –PivotTable from measures: layout, Show Values As, drill-down.
  • –TopN with RANKX; Pareto 80/20 with a cumulative % measure; ABC classification.
• Copilot & AI-assisted DAX
  • –Draft, explain and debug DAX measures with Copilot prompts in chat.
  • –Prompting with context: tables, relationships, grain, expected result — Copilot cannot see your model, so the context has to come from you.
  • –Copilot writes the first draft of every DAX measure; you paste it into Power Pivot, then refine, test and validate it.
  • –Validate every AI-written measure against known totals.

Build production-grade interactive dashboards and complete an end-to-end Capstone project.

• PivotTable reports
  • –Matrix reports: Sales/Profit by Date, Product, Customer and Region — field layout, Show Values As, and number format set at the measure.
  • –Conditional Formatting: Data Bars, Color Scales, Icon Sets.
  • –PivotCharts: Column, Line and Combo — with Pareto built as a combo chart plus a cumulative % line on a secondary axis, because Excel's built-in Pareto chart type is not available to PivotCharts.
• Dashboard design
  • –Layout fundamentals: Grid, Alignment, Spacing, Theme — a design system for colour, fonts and lines, with a standard Title, Subtitle and Notes block.
  • –Slicers and Timelines: styling, and one slicer driving every PivotTable and PivotChart through Report Connections.
  • –KPI cards and sparklines driven by CUBEVALUE and CUBEMEMBER — PivotTable Analyze > OLAP Tools > Convert to Formulas breaks a model PivotTable into cube formulas you can place anywhere on the canvas, and CUBEVALUE takes a slicer as an argument so every card still answers to the filters.
  • –The Camera Tool for a live picture of a report block — not on the ribbon, so add it from All Commands to the Quick Access Toolbar, or use Paste Special > Linked Picture instead.
• Production & maintenance
  • –Refresh strategy: Refresh All, background refresh and its limits, Refresh data when opening the file, and Refresh All pinned to the Quick Access Toolbar as your one-click button — no macro needed, so the file stays a plain .xlsx.
  • –Defending against schema drift, new columns and type changes.
• Capstone project
  • –Build → Model → Measure → Pivot → Dashboard → Present.
  • –Graded on accuracy, performance, Data Storytelling and design — presented on the afternoon of day 3, with written feedback to each participant after the course.
• Next steps
  • –Pre-deployment checklist; long-term file maintenance.
  • –Path forward: Dataflows, SharePoint, Power BI.
• Copilot for insights & storytelling
  • –Ask Copilot for summaries, trends and outliers — flatten the model's output onto a sheet as a Table first, because Copilot reads the grid, not the data model.
  • –Prompt for chart and PivotTable suggestions, and for executive-ready commentary.
  • –The analyst's guardrail: verify before you publish — data privacy basics, and when not to use AI.