← Back to Joy Hub
📗 Microsoft Excel 📊 Power BI

From Pivot Tables
to Power BI Dashboards

Your intermediate-to-advanced learning curriculum. Build on what you already know and level up to Power Query, DAX, and live dashboards.

Excel: Intermediate → Power BI: Intermediate → Goal: Advanced in both

Excel Curriculum

Excel — Level Up Track

You've got formulas and pivot tables down. These are the next skills that make you significantly more powerful — especially for data automation and reporting.

📗
Microsoft Excel — Intermediate to Advanced
8 modules · Building on formulas & pivot tables
1
Advanced Lookup Functions
Go beyond VLOOKUP. XLOOKUP handles left-lookups, multiple matches, and errors gracefully. INDEX/MATCH is still widely used — know both. Essential for merging data from multiple tables.
XLOOKUP INDEX/MATCH XMATCH
Intermediate
2
Dynamic Arrays & Spill Functions
Modern Excel's biggest upgrade. FILTER, SORT, UNIQUE, and SEQUENCE return arrays automatically — no more dragging formulas down. Transforms how you build reports.
FILTER SORT UNIQUE SEQUENCE
Intermediate
3
Power Query — Data Import & Cleaning
Connect to SharePoint, Excel files, CSVs, and databases. Clean messy data automatically — remove duplicates, fix formats, strip HTML, handle nulls. Builds repeatable pipelines that refresh with one click.
Power Query M Language basics SharePoint connector
Advanced
4
M Language — Custom Functions
Write reusable functions in Power Query's native language. Build custom HTML strippers, text cleaners, and conditional transformations — exactly the kind of pipeline work you've done before.
M Language Custom functions fnRemoveHtml pattern
Advanced
5
Advanced Pivot Tables & Pivot Charts
Calculated fields, slicers, timelines, and connected pivot charts. Add Power Pivot for multi-table data models that go far beyond what standard pivots can do.
Power Pivot Calculated fields Slicers
Intermediate
6
Data Validation & Conditional Formatting
Build dropdown-driven inputs, error-proof data entry, and visual dashboards that highlight exceptions automatically. Makes reports self-explanatory for leadership.
Data validation Conditional formatting Icon sets
Intermediate
7
Excel + SharePoint Integration
Connect Excel workbooks to SharePoint lists, sync data automatically, and manage refresh schedules. Critical for shared reporting workflows — keeps everyone working from the same live data.
SharePoint lists OneDrive sync Data refresh
Advanced
8
Macros & Basic VBA Automation
Record and edit macros to automate repetitive tasks — formatting reports, exporting files, running multi-step processes with a button click. VBA basics for light custom automation.
Macro recorder VBA basics Automation
Advanced

Power BI Curriculum

Power BI — Level Up Track

You've built reports — now master the data model, DAX calculations, and publishing dashboards that update automatically and look executive-ready.

📊
Microsoft Power BI — Intermediate to Advanced
8 modules · Building on existing report experience
1
Data Modeling — Star Schema
The foundation of good Power BI. Learn fact tables, dimension tables, and relationships. A proper star schema makes every report faster and every DAX formula simpler. Most common mistake is skipping this.
Star schema Relationships Cardinality
Intermediate
2
DAX Fundamentals
DAX (Data Analysis Expressions) is Power BI's formula language. Learn CALCULATE, SUM, SUMX, DIVIDE, and IF. These 5–10 functions handle 80% of real-world business calculations.
CALCULATE SUMX DIVIDE Measures vs Columns
Intermediate
3
Advanced DAX — Time Intelligence
Compare this month vs. last month, YTD vs. prior YTD, rolling 90-day averages. Time intelligence functions like DATEADD, SAMEPERIODLASTYEAR, and TOTALYTD are essential for exec dashboards.
DATEADD TOTALYTD SAMEPERIODLASTYEAR
Advanced
4
Power Query in Power BI
Same M language as Excel — if you learn it in one, you know it in both. Connect to SharePoint, Excel, SQL, and APIs. Build transformation pipelines that refresh automatically on a schedule.
M Language SharePoint connector Data refresh
Advanced
5
Dashboard Design for Leadership
Visuals that executives actually read. Learn when to use cards vs. charts vs. tables, how to use bookmarks for storytelling, and how to design a one-page executive summary that drives decisions.
Bookmarks Drill-through Themes & branding
Intermediate
6
Row-Level Security (RLS)
Control who sees what data in shared reports. Set up roles so leadership sees org-wide data while team leads see only their team. Essential for sharing reports across departments.
RLS roles Dynamic security Access control
Advanced
7
Publishing & Power BI Service
Publish to the web, set up scheduled refresh, share workspaces with colleagues, and embed reports in SharePoint or Teams. This is how reports go from your laptop to everyone else.
Power BI Service Scheduled refresh SharePoint embed
Intermediate
8
Power BI + Excel Together
Analyze Power BI datasets in Excel using Analyze in Excel. Connect live Power BI data to Excel pivot tables. Build a workflow where Excel handles raw data cleaning and Power BI handles visualization.
Analyze in Excel Live connections Hybrid workflow
Advanced

Quick Reference

Key Functions to Know

The functions that come up constantly. Bookmark this section.

Excel Functions

=XLOOKUP()
Modern replacement for VLOOKUP. Searches any direction, handles errors, returns multiple columns.
=FILTER()
Returns a filtered array based on conditions. No more complex array formulas for basic filtering.
=UNIQUE()
Returns distinct values from a list. Replaces manual deduplication in pivot tables.
=SORT()
Returns a sorted array dynamically. Combine with FILTER for live sorted filtered tables.
=LET()
Assign variable names inside a formula. Makes complex nested formulas readable and reusable.
=SUMIFS()
Sum with multiple conditions. Core of most financial summary calculations.
=IFERROR()
Catch errors cleanly. Wrap lookups so #N/A shows as blank or a default value.
=TEXT()
Format numbers/dates as text for display. Essential for building readable report labels.

DAX Functions — Power BI

CALCULATE()
The most important DAX function. Evaluates an expression in a modified filter context.
SUMX()
Iterates row-by-row and sums a calculated expression. Use for unit economics like cost × quantity.
DIVIDE()
Safe division — returns blank (not error) when dividing by zero. Use instead of the / operator.
DATEADD()
Shift dates for time comparisons. Use for month-over-month and year-over-year analysis.
TOTALYTD()
Cumulative year-to-date total. Essential for financial reporting and budget tracking.
RELATED()
Pull a value from a related table via a relationship. The DAX equivalent of a lookup.
FILTER()
Returns a filtered table. Used inside CALCULATE to apply complex conditions.
ALL()
Removes filters from a table or column — used to calculate % of total.

Free Learning

Best Free Resources

All free, all high quality. Mix YouTube with Microsoft Learn for the fastest results.

YouTube · Excel

Leila Gharani

The best Excel YouTuber for intermediate-to-advanced learners. Covers XLOOKUP, dynamic arrays, Power Query, and dashboard design. Clear, practical, no fluff.

YouTube →

YouTube · Power Query

Excel Off The Grid

Specialist channel for Power Query and M language. Perfect for building on the Power Query work you've already done. Deep, technical, excellent.

YouTube →

YouTube · Power BI

Guy in a Cube

The #1 Power BI YouTube channel. Adam and Patrick cover everything from beginner to advanced DAX, data modeling, and the Power BI service. Watch regularly to stay current.

YouTube →

YouTube · DAX

SQLBI — Marco & Alberto

The definitive DAX learning resource. Once you have the basics, these two are the gold standard for deep understanding of CALCULATE, context transition, and time intelligence.

YouTube →

Microsoft Learn

Power BI Learning Path

Free official Microsoft training. Structured learning path from data modeling to DAX to publishing. Earns badges. Good for filling gaps and learning in the right order.

Microsoft Learn →

Microsoft Learn

Excel Learning Path

Official Microsoft Excel training modules. Free, self-paced, covers dynamic arrays, Power Query, and data analysis. Great supplement to YouTube learning.

Microsoft Learn →

Reference

DAX.guide

The best DAX function reference. Every function documented with examples by the SQLBI team. Bookmark this — you'll use it every time you write DAX.

dax.guide →

Reference

Excel Jet — Function Reference

Clean, practical Excel function documentation with examples. Better than Microsoft's own docs for quick lookups. Great for dynamic array functions.

exceljet.net →

Fast Track

6-Week Study Plan

Realistic, around your actual work schedule. Mix watching with doing — the hands-on practice is where it clicks.

Weeks 1–2 · Excel

Modern Excel Functions

  • Watch Leila Gharani: XLOOKUP full tutorial
  • Practice FILTER, SORT, UNIQUE on real data
  • Watch: Dynamic arrays playlist (3–4 videos)
  • Build one report using only dynamic array functions
  • Learn LET() for cleaner nested formulas

Weeks 2–3 · Excel

Power Query Deep Dive

  • Watch Excel Off The Grid: Power Query series
  • Connect a SharePoint list to Excel via Power Query
  • Build a custom M function (build on what you know)
  • Set up an auto-refreshing data pipeline
  • Microsoft Learn: Power Query module

Weeks 3–4 · Power BI

Data Model + DAX Basics

  • Watch Guy in a Cube: star schema tutorial
  • Rebuild an existing report with proper data model
  • Learn CALCULATE — watch SQLBI intro video
  • Write 5 basic measures: totals, %, YTD
  • Bookmark dax.guide for reference

Weeks 5–6 · Power BI

Dashboards + Publishing

  • Watch time intelligence series (Guy in a Cube)
  • Build a month-over-month comparison dashboard
  • Publish to Power BI Service, set up refresh
  • Embed a report in SharePoint
  • Set up Row-Level Security for a shared report

Quick Reference

Key Terms Glossary

Power Query

Excel & Power BI's ETL tool. Extract, transform, and load data from any source. Automates data cleaning.

M Language

The formula language behind Power Query. Functional language for building custom data transformation steps.

Dynamic Arrays

Modern Excel feature where formulas return ranges automatically. FILTER, SORT, UNIQUE all use this.

Spill Range

When a dynamic array formula outputs to multiple cells automatically. Referenced with the # operator.

DAX

Data Analysis Expressions — Power BI's formula language. Used to write measures and calculated columns.

Measure

A DAX calculation evaluated at query time. Changes based on filters. This is what you use for KPIs.

Calculated Column

A DAX calculation stored row-by-row in the table. Use for categorization, not aggregation.

Star Schema

Data model pattern with one fact table (transactions) and multiple dimension tables (dates, products, people).

Filter Context

The set of filters applied when a DAX formula evaluates. Understanding this is the key to mastering DAX.

Cardinality

The uniqueness of values in a relationship column. One-to-many is the most common and preferred pattern.

Row-Level Security

Controls what data each user can see in a shared Power BI report. Set up roles in Power BI Desktop.

ETL

Extract, Transform, Load — the process of pulling data from a source, cleaning it, and loading it for analysis.