Advanced Excel: Power Query, Power Pivot and DAX

In this lesson, you’ll master a complete, repeatable process for bringing raw sales data from a CSV file into Excel and preparing it for analysis using modern data tools. By the end, you will be able to confidently import a CSV file that contains transactional sales information, handle common data issues during import, and structure the data so it’s ready for advanced reporting and modeling.

You will practice using Excel’s data connectivity features to locate and connect to a CSV file stored on your computer or network. From there, you’ll see how to use Power Query’s interface to preview the incoming sales data, choose or change the file delimiter, set the correct data types for dates, numbers, text, and currency, and manage column headers. You’ll also learn how to handle typical CSV problems such as extra blank rows, misaligned columns, encoding issues, and unexpected characters, ensuring that what lands in Excel is clean and reliable.

The session demonstrates how to load the imported sales table either directly into an Excel worksheet or as a connection-only query for use in a data model, setting the foundation for later analysis with PivotTables, Power Pivot, and DAX. You’ll understand the implications of each loading option, so you can decide the best approach depending on file size, refresh requirements, and reporting needs.

The specific technologies used in this lesson are Microsoft Excel (Office 365 or Excel 2016 and later), the Get & Transform Data features powered by Power Query, and CSV file connectors built into Excel’s Data tab. Everything is done within the Excel environment—no external software is required beyond your CSV sample files.

This lesson is designed for professionals who work with recurring sales reports, including business analysts, financial analysts, sales and marketing specialists, small business owners, and operations or reporting staff who frequently receive CSV exports from CRM systems, ERPs, e‑commerce platforms, or point‑of‑sale systems. It’s also suitable for learners who are comfortable with basic Excel and now want to move into more structured, automated data preparation workflows that support deeper analysis in subsequent modules.

In this lesson, you’ll master how to connect Excel’s modern data tools to a SQL database and bring in clean, structured data ready for analysis. By the end of the session, you’ll be able to set up a reliable connection to SQL Server (and other SQL-based systems), choose and filter the tables or views you need, and load that data efficiently into Excel’s data model for advanced reporting and analytics.

You’ll walk through every step of the process: configuring the connection, authenticating securely, selecting the right objects, transforming columns and rows as needed, and deciding whether to load the results into a worksheet, into the data model, or both. You’ll also learn best practices for refreshing data from SQL, so that your reports and dashboards stay up to date with minimal manual effort.

The lesson uses Excel’s integrated business intelligence stack, focusing on:

- Power Query (Get & Transform Data) to connect to and shape data from SQL
- The data model (Power Pivot backend) to store imported tables efficiently
- Basic exposure to DAX concepts for later use with SQL-imported data

The primary technology demonstrated is Microsoft Excel (modern versions with Power Query and data model support) connecting to SQL Server, with notes on connecting to other SQL platforms that share similar connection steps.

This lesson is designed for analysts, finance and accounting professionals, business users, data enthusiasts, and Excel power users who need to pull data directly from SQL databases rather than relying on static exports. It’s also well-suited for those working with IT or database teams who want to understand how to self-serve data from SQL into Excel, laying the groundwork for more advanced modeling and DAX calculations in later lessons.

In this lesson, you’ll master how to bring together multiple files stored in a common directory and turn them into a single, clean data source using Power Query. By the end, you’ll be able to connect Excel to a folder, automatically combine all compatible files inside it, and set up a refreshable process that updates whenever new files are added or existing ones are changed—without having to repeat manual imports.

You will walk through each step of the workflow: browsing to and connecting with the folder, understanding the file preview and metadata, filtering which files to include (for example, by extension, date, or name pattern), and using the “Combine & Transform” options to merge many files into one structured table. You’ll learn how to handle inconsistent column names or layouts, manage headers, promote data types correctly, and resolve common problems that occur with folders of CSV or Excel files. The lesson also shows how to parameterize folder paths so your queries remain portable across different environments or users.

The primary technologies demonstrated are Microsoft Excel’s integrated data tools, specifically Power Query (Get & Transform). You’ll see how to use the folder data connector, the Query Editor interface, applied steps, and the automatic sample query that Power Query builds when combining files. The processes you build here can later feed into data models for analysis with measures and calculations, so this lesson forms a practical bridge between raw file storage and robust analytical models.

This lesson is designed for users who already have a basic familiarity with spreadsheets and want to streamline their reporting process. It is especially relevant for business analysts, financial professionals, accountants, operations and sales analysts, data enthusiasts, and anyone who receives recurring files (monthly reports, weekly exports, regional files, or departmental spreadsheets) and needs a reliable, automated way to consolidate them in Excel for further reporting and analysis.

In this lesson focused on importing data from XML files, learners discover how to bring structured XML data efficiently into Excel using modern data tools. By the end of the session, participants will be able to connect to XML sources, interpret their hierarchical structure, and transform that information into clean, tabular formats ready for analysis and reporting.

You will practice using Excel’s data import interfaces with particular emphasis on Power Query. The lesson walks through connecting to a local XML file, understanding the applied steps, expanding nested nodes, and shaping the data into a relational layout. You’ll also see how this XML-based data can feed into the data model to support advanced calculations and dashboards later in the program, ensuring a smooth pipeline from raw XML to analysis-ready data.

The primary technologies demonstrated in this lesson are Excel’s Get & Transform (Power Query) for importing and reshaping XML data, and the Excel data model as the destination for the cleaned dataset. Where relevant, the session touches on how this prepared XML data can later be used with Power Pivot and DAX measures for deeper analytics.

This lesson is designed for professionals who routinely receive or manage structured data from business systems, APIs, or third-party providers in XML format. It is particularly valuable for data analysts, reporting specialists, finance and accounting professionals, business intelligence users, and anyone who wants to transform XML files into robust, reusable datasets inside Excel without manual copy-paste or complex coding.

By the end of this lesson, learners will have a clear, practical understanding of the Power Query Editor workspace in Excel and how to navigate it efficiently for data transformation and preparation. You’ll be able to confidently locate and use the key panels and menus, understand what each area of the editor does, and move seamlessly between them as you clean and shape data for analysis with Power Query, Power Pivot, and DAX.

You will explore every major component of the Power Query Editor window in detail: the ribbon and its main command groups, the Queries pane, the central data preview grid, the formula bar, and the Applied Steps pane. You’ll learn how to interpret the query preview, how to see which transformations have been applied, and how to step backward and forward through your data shaping process using the applied steps interface. By the end of the lesson, you’ll not only recognize the layout but also know how to use it to track, audit, and adjust your transformations without breaking your queries.

The session focuses on the Excel implementation of Power Query, including its built‑in interface and controls. You will see how the editor integrates into the wider data modeling workflow, preparing your data so it can later be loaded into the Excel Data Model and used with Power Pivot and DAX calculations. The tools demonstrated include the Power Query ribbon tabs (Home, Transform, Add Column, View), column and row context menus, and options for managing query properties directly within the editor layout.

This lesson is designed for professionals and students who already use Excel for reporting or analysis and are ready to move into more advanced, model‑driven workflows. It is ideal for data analysts, business and financial professionals, BI beginners, and power users who want to build robust data models and automated reporting processes. No prior coding knowledge is required; the lesson focuses on understanding the user interface so that newcomers to Power Query can progress smoothly into more advanced transformations and later modules on data modeling and DAX.

In this lesson on preliminary checks before transforming data in the Power Query environment, learners discover a structured pre-transformation checklist that prevents errors, data loss, and misleading results later in their analytics workflow. By the end of the session, they will be able to systematically review and validate data before applying any major shaping, cleaning, or merging steps, setting a strong foundation for efficient use of query-based automation and DAX-powered models.

Learners will practice how to quickly assess data quality and structure as soon as a new dataset is loaded. This includes confirming that all expected columns are present, checking row counts against source systems, and identifying missing or duplicate records. They also learn how to validate data types for each field—ensuring that numbers, dates, text, and logical values are correctly recognized—so that subsequent calculations, aggregations, and relationships behave as expected.

The lesson covers how to spot common problems such as unexpected nulls, formatting inconsistencies, hidden headers or subtotals, mixed data types in a single column, and “dirty” columns that combine multiple pieces of information. Learners will see how these issues can break joins, distort pivots, or cause incorrect measures later, and how to flag them early using built-in query previews, filters, and sort options.

A key focus is on understanding the impact of source structure: learners will review how to check if their source files or tables are stable (consistent sheet names, column positions, and file paths) before investing time in complex transformations. They will also learn to identify whether a dataset is transactional, aggregated, or dimensional in nature, and why that matters when planning both query steps and downstream calculations.

In terms of tools and technologies, the session is hands-on with the Power Query interface embedded in modern spreadsheet environments and the data model layer reachable from there. Learners will work with the Query Editor window, the applied steps pane, column profiling tools, and basic query settings to perform their checks. While advanced calculations and modeling are explored in other parts of the program, this lesson stays focused on the front-end query layer and its quality-control capabilities.

This lesson is designed for professionals and students who already use spreadsheets for reporting or analysis and want to progress toward more robust, semi-automated data preparation. It is ideal for analysts, business users, finance professionals, data enthusiasts, and self-taught users who are comfortable with basic formulas and pivot tables but need a reliable method to evaluate data before building reusable queries and advanced models.

In this lesson on merging and appending query concepts, learners move beyond basic data loading and step into powerful techniques for combining and shaping information from multiple sources. By the end of the session, they will be able to confidently bring together tables that share common fields, as well as stack similar datasets on top of each other to build comprehensive, analysis-ready models.

Learners will gain hands-on experience with two core skills:

- Using merge operations to join tables based on matching columns, understand the difference between inner, left, right, full, and other join types, and decide which join logic best fits their business questions.
- Using append operations to consolidate multiple tables with the same structure into a single, unified query, ideal for scenarios such as monthly files, regional reports, or departmental exports.

They will also learn how these combining actions impact data shaping: controlling which columns are retained, managing duplicate or missing records created during joins, and structuring queries so downstream analysis in pivot-based models and DAX calculations becomes cleaner and more efficient.

This lesson makes extensive use of the Excel-integrated data preparation environment, focusing on the query editor interface for building merges and appends step by step. Learners will work with real-world style datasets inside Excel, applying transformations and seeing immediately how their operations affect the underlying data model.

The material is designed for professionals, analysts, and advanced spreadsheet users who already know basic formulas and pivot tables and now want to automate repetitive data preparation tasks. It’s especially suitable for those responsible for recurring reports, dashboard creation, or multi-source data consolidation who need a more robust, scalable approach to combining and shaping data within Excel’s modern analytics ecosystem.

In this lesson on appending in Power Query, learners dive into how to efficiently stack and combine multiple data tables into a single, analysis-ready dataset. By the end of the session, they will be able to confidently use append operations to bring together monthly files, regional reports, or repeated exports from business systems without manual copy‑paste or error-prone workarounds.

Learners will practice identifying when to use Append versus Merge, handling tables with matching columns and those with partially mismatched structures, and ensuring column names and data types are aligned for clean results. They will see how to append tables from different sources (such as multiple Excel files or sheets), manage column order and data type consistency, and troubleshoot common issues like missing fields or extra columns. The lesson also covers best practices for naming steps and queries so that ongoing maintenance and updates become simple and transparent.

The primary technology used in this lesson is Power Query within Excel. All demonstrations use the Power Query interface, including the Append Queries feature, query editor steps, and applied steps pane. While the focus is on the Power Query environment in Excel, the techniques and concepts apply equally well to scenarios where the resulting tables will later feed into data models and DAX-based calculations.

This lesson is ideal for professionals who already work with spreadsheets and want to elevate their data preparation skills—business analysts, finance and accounting staff, operations and sales reporting professionals, data enthusiasts, and anyone responsible for regularly consolidating multiple reports or files. It is particularly valuable for learners who are tired of manual consolidation and want a repeatable, automated process for combining and shaping their data before moving on to more advanced modeling and analysis.

By the end of this lesson on merging queries, learners will be able to confidently combine multiple data tables into a single, analysis-ready dataset using robust join logic. You’ll understand how to choose the right type of merge (Left, Right, Inner, Full, Anti) for different business questions, how to merge on single or multiple key columns, and how to deal with mismatched or duplicate keys without corrupting your model. You’ll also learn practical techniques to expand and clean merged columns, prevent column name conflicts, and preserve query performance as your data grows.

This lesson focuses on the query editor environment within Excel, specifically using the merge functionality to join tables from different sources. You’ll work directly with the graphical interface for building merge steps, while also seeing how those steps are represented in the underlying M language. The examples use typical business data sources such as sales tables and dimension lookups to mirror real reporting scenarios.

The material is designed for analysts, finance and accounting professionals, operations and supply-chain specialists, business intelligence practitioners, and anyone who regularly works with multiple related datasets in spreadsheets. It is especially relevant if you already use pivot-based reporting or dashboards and now need a more controlled, repeatable way to combine and shape data before loading it into your models.

In this lesson on managing rows and columns in Excel’s modern data model environment, learners dive deep into practical techniques for reshaping, structuring, and controlling tabular data so it’s ready for analysis and reporting.

By the end of this session, learners will be able to:
- Confidently add, remove, and reorder columns to design clean, analysis‑ready tables.
- Filter and remove unwanted rows (such as blanks, errors, duplicates, and outliers) using repeatable, step‑based logic.
- Split, merge, and transform columns to standardize messy data and derive new fields.
- Use conditional logic and custom columns to create calculated attributes from existing data.
- Apply best practices for naming, organizing, and documenting steps so transformations are easy to follow and maintain.
- Build a repeatable data‑cleaning pipeline that can be refreshed instantly when new source data arrives.

The tools and technologies covered in this lesson include:
- Power Query for shaping, filtering, and transforming rows and columns through an applied-steps interface.
- Power Pivot’s data model view for working with calculated columns and for structuring tables for downstream measures.
- DAX calculated columns where appropriate, to complement transformations and enrich datasets.
- Core Excel table features that support structured references and consistent data layouts.

This lesson is intended for:
- Data analysts and business intelligence professionals who routinely work with large or messy datasets and need a reliable, refreshable process for cleaning them.
- Financial analysts, accountants, operations analysts, and reporting specialists who want to automate repetitive row/column cleaning tasks.
- Excel power users transitioning into a more model‑driven approach who want to integrate Power Query and DAX into their daily workflow.
- Professionals preparing to build dashboards, KPI reports, or self‑service analytics solutions and who need a solid foundation in robust data preparation techniques.

In this lesson, you’ll master how to break complex data into clean, usable pieces using the Split Columns feature in Excel’s modern data tools. By the end of the session, you will be able to confidently separate messy, combined fields—such as full names, addresses, codes, and delimited text—into structured columns that are ready for analysis and modeling.

You’ll learn how to:
- Split text by delimiters (commas, spaces, custom characters, line breaks) to isolate key components of your data.
- Use splitting by number of characters or fixed width when dealing with standard codes or IDs.
- Handle real-world examples like splitting “First Last” names, “City, State, ZIP” strings, SKU or product codes, and multi-part reference numbers.
- Apply advanced options such as splitting at the left-most, right-most, or each occurrence of a delimiter.
- Automatically detect and correct inconsistent data during splitting (e.g., extra spaces, variable lengths, missing bits).
- Incorporate split steps into a repeatable data-cleaning workflow so that each refresh of your data applies the same logic automatically.

The technologies demonstrated focus on Excel’s modern analytics layer, especially the query-based environment for data import, transformation, and loading. You will see exactly how to perform column splitting through the query editor interface, and how these transformations are recorded as steps that can be reused and edited. The lesson also shows how this cleaning step prepares your tables for downstream use in analytical models and formulas that work across related tables.

This lesson is designed for:
- Business analysts and data professionals who routinely import CSVs, system exports, or text-heavy fields and need to prepare them for reporting.
- Excel users transitioning from basic formulas to more scalable, refreshable data transformation workflows.
- Finance, operations, marketing, and HR professionals who work with large, messy datasets and want to reduce manual data cleaning.
- Anyone building dashboards, reports, or data models who needs clean, well-structured columns as a foundation for accurate measures and calculations.

By the end, you’ll be able to transform tangled text fields into organized, analysis-ready columns with just a few clicks, and integrate this step seamlessly into your broader data preparation process.

In this lesson, you’ll master one of the most frequently used data-cleaning features in Power Query: the **Replace Values** option. By the end of the session, you’ll be able to confidently identify, standardize, and correct inconsistent entries across large datasets, turning messy, unreliable data into clean, analysis-ready tables for use in Power Pivot models and DAX-based reporting.

You will learn how to:
- Use the Replace Values command to fix typos, spelling variations, and inconsistent labels (e.g., “NY”, “New York”, “N.Y.”).
- Replace nulls, blanks, and error values with meaningful defaults or placeholders.
- Apply replacements to specific columns or across multiple fields strategically to keep data structure intact.
- Implement conditional and pattern-based replacements using transformations within the query steps.
- Document your replacements so they are automatically repeated every time you refresh your Power Query queries.
- Avoid common pitfalls such as unwanted global replacements that corrupt valid data.

This lesson works directly inside the **Power Query Editor** within modern Excel. You’ll see the Replace Values feature in action as part of a full data-cleaning workflow, integrated with other transformation tools and preparing your data for downstream **Power Pivot** models and **DAX** measures. All demonstrations are done using practical, business-style datasets so you can immediately apply the techniques to your own workbooks.

The content is designed for:
- Intermediate Excel users who are moving beyond basic formulas and want to automate data preparation.
- Analysts and business professionals who regularly import CSV, text, or database exports and need to standardize messy inputs before building reports.
- Anyone working with data models and dashboards in Excel who wants consistent, trustworthy fields for aggregations, relationships, and DAX calculations.

If you work in roles such as data analysis, reporting, finance, operations, or business intelligence and are ready to streamline your data-cleaning process, this lesson will show you how to leverage the Replace Values functionality as a core part of your Power Query transformation toolkit.

In this lesson focused on the Group By feature inside Power Query, learners will master how to summarize and restructure raw data into meaningful, analysis-ready tables. By the end of the session, they will be able to confidently perform grouping operations on large datasets, calculate key summary statistics, and build cleaner, more compact tables that feed directly into advanced reporting and dashboarding workflows.

Learners will discover how to group records by one or multiple columns, apply aggregations such as sums, averages, counts, minimums, and maximums, and use both basic and advanced Group By modes. They will also learn how to combine Group By with other transformation steps—such as filtering, sorting, and custom columns—to create robust, repeatable data preparation processes. The lesson will show how these grouped and aggregated tables become the foundation for more powerful modeling and analysis with Pivot-based data models and DAX measures later in the program.

This session is hands-on and uses the Power Query editor inside modern spreadsheet environments (including the Get & Transform Data tools in desktop Excel). Learners will work directly with the Group By dialog and the underlying query steps, seeing how each transformation is recorded and can be modified or reordered within the query settings pane. The technologies highlighted include the Power Query interface, its transformation commands, and the M-based query logic that’s generated automatically in the background.

The material is designed for analysts, business professionals, finance and accounting users, operations and logistics staff, marketers, and anyone who regularly works with tabular data and needs to transform large, messy source files into structured, summarized datasets. It is particularly suited to those who are already comfortable with basic spreadsheets and want to progress into more automated and scalable data preparation techniques, as well as learners preparing to leverage Pivot-based models and DAX calculations in later sections.

In this lesson on Pivoting and Unpivoting Theory, learners will develop a solid conceptual and practical understanding of how to reshape tabular data for analysis. By the end of the session, they will be able to clearly distinguish between pivoting (converting row values into columns) and unpivoting (converting columns into rows), identify when each technique is appropriate, and anticipate how these transformations affect subsequent calculations and reports. Learners will be able to take messy, cross‑tab or “report-style” datasets and redesign them into clean, analysis-ready tables that work seamlessly with formulas, aggregations, and data models. They will also understand common pitfalls such as duplicate keys, aggregation issues, and data loss, and know how to avoid or correct them. Ultimately, they will be capable of planning a full transformation workflow that uses pivoting and unpivoting as core steps in their data cleaning process.

The lesson focuses on the tools used to perform these reshaping operations in modern spreadsheet-based analytics. Learners will see how pivoting and unpivoting are implemented using the transformation features in the query editor interface, starting from imported raw data through to a structured output suitable for modeling. The session connects these transformations to later steps in the analytics pipeline, showing how well-shaped tables integrate with data models and expression-based calculations. Although the emphasis is on conceptual theory, the lesson is grounded in practical interfaces and commands that learners will encounter in real-world workbooks and dataflows.

This content is designed for professionals and students who already have a working knowledge of spreadsheets and want to advance into more serious data preparation and modeling. It is especially relevant for analysts, accountants, business intelligence practitioners, and power users who frequently receive data in the wrong layout: monthly figures spread across many columns, repeated headers, or legacy reports exported from line-of-business systems. Anyone who needs to turn those awkward files into consistent, tidy tables that support reliable reporting, dashboards, and advanced calculations will benefit from this lesson.

In this hands-on session focused on “Pivoting Practical,” learners dive deep into real-world techniques for reshaping and reorganizing messy data into clean, analysis-ready formats using advanced Excel features. By the end of the lesson, you’ll be able to confidently transform irregular data layouts into structured tables that work seamlessly with later analytical steps and dashboards.

You will learn how to recognize when your data needs pivoting versus unpivoting, how to identify ideal row and column structures for reporting, and how to convert cross-tab or matrix-style source files into tidy tabular datasets. The lesson walks you step by step through building practical transformation queries, so you can group, reshape, and reorganize your information without breaking links to the original sources. You’ll practice turning multi-header sheets, report exports, and manually created tables into consistent, normalized layouts that are suitable for aggregation, time-series analysis, and advanced measures.

This session uses Excel’s modern data tools, with a particular emphasis on Power Query’s pivot and unpivot transformations inside the Get & Transform Data experience. You will see how these tools interact with the broader data-cleaning workflow, including loading results into the data model for use alongside Power Pivot calculations and DAX measures in later parts of the program. The focus is on practical application: you’ll perform actual pivoting operations on example datasets, apply key settings and options, and understand how each transformation affects downstream analysis.

The lesson is designed for professionals and students who already have a working knowledge of Excel and want to streamline their data-preparation process. It’s suitable for analysts, business users, finance and accounting staff, operations teams, and anyone who regularly receives poorly structured spreadsheets from different systems and needs to prepare them for reporting. If you’re aiming to move beyond manual copy‑paste cleanup and build reliable, repeatable data-cleaning workflows for your daily reporting and analytics, this lecture is tailored to you.

In this lesson, learners dive deep into the Extract tool inside Excel’s modern data automation stack, focusing on how to pull out exactly the text elements they need from messy, inconsistent data. By the end of the session, you’ll be able to confidently extract key values such as IDs, codes, names, prefixes, suffixes, domains, and patterns from larger strings, and turn unstructured or semi-structured inputs into clean, analysis-ready columns.

You’ll work through practical scenarios like splitting product codes into components, isolating numeric values from text, extracting email domains, carving out dates or reference numbers buried inside descriptions, and standardizing text patterns for reporting. The lesson emphasizes repeatable, refreshable solutions instead of manual formulas, so that when your source data updates, the transformations automatically apply without rework.

The core technologies demonstrated in this lecture are Excel’s query and modeling tools: specifically, the Extract functions within Power Query’s user interface and the underlying M transformations that support them. You’ll see how to apply commands such as extracting text before or after a delimiter, between specific characters, from the start or end of a string, and based on fixed positions or patterns, all within a structured query step that becomes part of your data-cleaning workflow.

This content is designed for professionals and learners who already work with spreadsheets and want to move beyond basic functions into more advanced, automated data preparation. It’s especially relevant for analysts, business intelligence users, accountants, finance and operations staff, data-minded managers, and anyone responsible for cleaning large or recurring datasets who needs precise control over text extraction as part of a robust data transformation process.

In this lesson on “Format Text Options,” learners dive deep into the text-cleaning features that dramatically improve the accuracy and professionalism of their data models. By the end of the session, participants will be able to restructure messy text fields into clean, standardized, analysis-ready columns.

You’ll learn how to:
- Use built-in text formatting commands to quickly standardize capitalization (UPPER, lower, Proper case) across large datasets.
- Clean imported data by trimming extra spaces, removing non-printable characters, and handling irregular spacing between words.
- Extract, join, and restructure text using functions such as LEFT, RIGHT, MID, FIND, LEN, and CONCAT/CONCATENATE, so you can split IDs, isolate codes, or combine fields into readable labels.
- Apply conditional text transformations to tidy up inconsistent entries (e.g., replacing unwanted prefixes/suffixes, standardizing abbreviations, or cleaning up imported codes).
- Integrate these text transformations into repeatable workflows so that every refresh of your data stays consistently formatted and ready for downstream analysis and modeling.

The technologies used in this lesson include:
- Core Excel worksheet functions focused on text manipulation and formatting.
- Query-based text transformation steps to automate and repeat text-cleaning logic.
- Formatting tools from the Excel interface that help enforce consistent presentation and structure across large tables.

This lesson is designed for:
- Analysts and business professionals who regularly work with exported or downloaded text-heavy datasets (from ERPs, CRMs, web tools, or databases) and need them cleaned for reporting.
- Financial, marketing, operations, and HR professionals who build dashboards or summary reports and must ensure that names, categories, and codes are consistent.
- Beginner-to-intermediate Excel users who already understand basic formulas but want more control over text data to support more advanced modeling and reporting tasks later in the program.

In this lesson on **Number Transformations**, learners dive deep into the numeric cleaning and reshaping capabilities available in modern Excel’s data modeling and querying tools. By the end of the session, they will be able to confidently convert messy, inconsistent numeric fields into clean, analysis-ready measures that plug seamlessly into reports, dashboards, and advanced data models.

**What learners will be able to do by the end of this lesson**

After completing this lesson, learners will be able to:

- Detect and fix common numeric issues such as text-stored numbers, unexpected decimals, and inconsistent formats.
- Convert numbers between data types (e.g., text to number, whole number to decimal, percentage conversions) without breaking existing queries or measures.
- Standardize number formats across large datasets, including currency, percentages, and custom formats, to support consistent reporting.
- Perform arithmetic transformations at scale (add, subtract, multiply, divide) on columns or subsets of data using transformation steps rather than manual formulas.
- Handle negative values and sign corrections (e.g., convert negative values to positive, flip signs, manage parentheses-style negative numbers).
- Apply rounding techniques (ROUND, ROUNDUP, ROUNDDOWN, and banker’s rounding equivalents) in a repeatable, step-based way.
- Normalize and scale numeric values (e.g., rebase indices, convert units, rescale metrics) for comparison across different tables and sources.
- Build repeatable transformation logic so that when source data refreshes, all numeric cleaning is automatically re-applied without manual intervention.
- Trace, reorder, or edit applied numeric transformation steps to troubleshoot calculation errors or adjust logic for new business rules.

**Tools and technologies used in this lesson**

To achieve these outcomes, the lesson walks through hands-on examples using:

- **Power Query (Get & Transform)**
- Applying numeric transformation steps within the query editor.
- Changing data types to numeric formats.
- Using column transformations such as Standard (Add, Subtract, Multiply, Divide), Rounding, and Statistical operations on numeric fields.
- Creating custom columns for more complex numeric logic.

- **Power Pivot data model**
- How cleaned numeric columns from queries feed into the data model.
- Considerations when using transformed numbers as measures, calculated columns, or relationships.

- **DAX (Data Analysis Expressions)**
- Using DAX to refine or complement numeric transformations from queries—especially for scenario-based calculations, dynamic KPIs, and calculated measures.
- Applying numeric functions in DAX for additional control over rounding, scaling, and conditional transformation logic.

The lesson emphasizes how these components work together: Power Query for upstream numeric cleaning, the data model for structured storage, and DAX for dynamic analytics.

**Intended audience**

This lesson is designed for:

- **Intermediate and advanced Excel users** who already work with formulas and basic data tools and want to elevate their numeric cleaning skills using Power Query, modeling, and DAX.
- **Data analysts and business analysts** who regularly import data from multiple sources (CSV, databases, exports from line-of-business systems) and need reliable, automated numeric transformations.
- **Financial, operations, and reporting professionals** who manage large tables of metrics, KPIs, budgets, and forecasts and must ensure numerical accuracy and consistency before building reports or dashboards.
- **Self-service BI users and Excel power users** preparing to build robust data models and interactive dashboards, and who need to understand how to prepare numeric data for downstream analysis.

Anyone who works with large, messy datasets and wants to move beyond manual, cell-by-cell fixes into structured, refreshable numeric transformation workflows will benefit strongly from this lesson.

In this lesson on **Date Time Transformations**, you’ll master how to convert messy, inconsistent date and time fields into accurate, analysis-ready data directly inside Excel’s modern data modeling tools. By the end of the session, you’ll be able to import, clean, standardize, and enrich date/time columns so they can drive reliable metrics, dashboards, and time‑intelligence calculations.

You will learn how to:
- Detect and fix common date/time issues (text dates, mixed formats, wrong locales, time zones).
- Convert text strings into true date, time, and datetime data types.
- Extract specific components such as year, quarter, month, week number, weekday, hour, and minute.
- Create calculated date-related columns for reporting, such as fiscal periods, month names, and custom week definitions.
- Normalize date/time values for consistent aggregation and comparison across multiple data sources.
- Prepare robust date fields that work seamlessly with advanced measures and time-intelligence logic in later analytics.

The lesson focuses on practical, hands-on use of:
- **Power Query** for transforming raw date/time fields during data import and cleaning (splitting columns, changing types, extracting parts, handling locale-specific formats).
- **Power Pivot** for building date-related calculated columns in the data model so that fact tables can connect to date dimensions.
- **DAX** for creating simple date-based expressions that rely on correctly structured date/time data (a foundation for more advanced time-series analysis later in the program).

This session is designed for learners who already work with Excel and want to move beyond basic formulas and static spreadsheets into more powerful data modeling and automation. It’s especially valuable for:
- Business analysts and reporting specialists who rely on accurate date-based reporting (monthly KPIs, quarterly results, year‑over‑year comparisons).
- Finance, operations, marketing, and HR professionals who regularly analyze trends over time.
- Data enthusiasts and Excel power users who need clean, standardized date/time data for dynamic dashboards and advanced analytics.

By completing this lesson, you’ll be ready to handle real‑world date and time data in complex workbooks and data models, ensuring your time-based reports are both consistent and trustworthy.

In this short orientation lesson, you’ll discover how the upcoming roleplay scenario will help you move from simply knowing DAX theory to actually applying it in realistic business situations. By the end of the session, you’ll understand exactly how the exercise will run, what part you’ll play, and how it will strengthen your skills for real-world analytics and reporting.

You’ll learn how the roleplay will simulate a conversation with stakeholders who need data-driven insights from your Excel data model. You’ll see how you’ll be expected to think like an analyst: clarifying business questions, deciding which measures to build, and choosing the right DAX calculations to support decision-making. This lesson sets clear expectations on how you’ll use calculated columns, measures, and row context vs. filter context in a practical scenario, so that when the roleplay starts, you can focus on applying the logic instead of guessing what to do.

The session walks through how DAX will be used inside Power Pivot and the Excel data model during the roleplay. You’ll see where you’ll write your expressions, how those formulas will flow through PivotTables and reports, and how your work in previous lessons on relationships and basic functions will come together. There is no new syntax introduced here; instead, the focus is on how you will combine what you already know into a coherent, business-facing solution.

This lesson uses Microsoft Excel with the Data Model and Power Pivot enabled, and it relies heavily on DAX within that environment. You’ll see how the roleplay will make you work inside the Power Pivot window, write and test DAX expressions, and then surface your results in standard Excel reporting views. While Power Query and other BI tools are part of the broader learning path, this specific lesson centers on the data model and DAX layer that you’ll use during the roleplay.

The orientation is designed for learners who already have at least a basic grasp of measures, calculated columns, and evaluation context and want to sharpen their practical skills. It’s especially valuable if you are a business analyst, financial or operations professional, Excel power user, or anyone preparing to support BI or reporting tasks in a corporate environment. If you’re eager to bridge the gap between conceptual DAX knowledge and confident, business-ready application in Excel, this lesson prepares you to get maximum value from the upcoming roleplay exercise.

In this lesson, you’ll discover how to leverage the **Add Column from Examples** feature in Excel’s Power Query to build custom columns without writing complex formulas. By the end, you’ll be able to transform and reshape your data simply by providing a few sample outputs, letting Power Query infer the logic for you.

You will learn how to:
- Create new columns in Power Query by typing example values rather than writing M code.
- Automatically generate text transformations such as splitting, combining, extracting, and formatting text (e.g., pulling out first names, extracting codes, standardizing date formats).
- Derive numeric columns from existing data, including conditional outputs and custom calculations.
- Build date and time-related columns (such as year, month name, or custom period labels) using examples.
- Review, understand, and refine the M code that Power Query generates from your examples, so you can tweak or reuse the logic later.
- Recognize when “column by example” is the most efficient approach and when a traditional custom column formula is more appropriate.

The primary technology you’ll work with in this lesson is **Excel’s Power Query Editor**, specifically:
- The **Add Column from Examples** command (both “From All Columns” and “From Selection”).
- The generated **M language** steps that Power Query creates behind the scenes.
This lesson assumes you’re working in a modern version of Excel that includes the full Power Query experience.

This content is ideal for:
- Excel users who are already comfortable with basic data cleaning but want a faster, more intuitive way to build transformation logic.
- Analysts and business professionals who frequently prepare data for pivot tables, dashboards, or Power Pivot models and want to reduce manual formula-writing.
- Learners transitioning from traditional formulas to Power Query who prefer example-driven, “show me” methods instead of coding.
- Anyone working with repetitive text, date, or numeric transformations who wants to automate these tasks and make their data preparation more robust and repeatable.

In this lesson, learners dive deeper into building powerful calculated custom columns inside Excel’s data modeling environment, focusing on turning raw, imported data into analytics-ready metrics. By the end, participants will be able to design and insert new calculated fields that don’t exist in the source tables, allowing them to enrich their models with logic-driven numbers, ratios, and business KPIs.

You will learn how to construct calculated expressions step by step, understand when to use calculated columns instead of measures, and apply row-level logic such as conditional calculations, text manipulations, and date-based formulas. The session walks through practical scenarios like creating profit and margin columns, categorizing records based on business rules, and combining multiple fields into one analytical attribute. Emphasis is placed on avoiding common mistakes (like wrong data types or circular logic), ensuring your added columns refresh smoothly with the rest of the model.

This lecture makes extensive use of modern Excel capabilities, including the data model, the formula engine used in data analysis expressions, and the modeling interface where you define and manage calculated columns. You’ll see how these components work together to enhance pivot-based reporting and dashboarding.

The content is designed for analysts, business intelligence users, accountants, finance professionals, and power users who already have a basic understanding of working with queries and data models in spreadsheets and are ready to extend their reporting with custom, calculation-driven fields. It’s also well-suited to anyone preparing for analyst roles who needs to manipulate data beyond simple formulas and wants to build more robust, reusable analytical models directly in Excel.

In this lesson on **Conditional and Index Column**, learners dive deeper into transforming and enriching datasets using the advanced capabilities of Excel’s modern data tools. By the end of the session, you will be able to design more intelligent query logic and create robust, analysis-ready tables that integrate seamlessly with downstream models and reports.

You will learn how to build conditional logic directly in your queries—similar to nested IF statements, but far more scalable and easier to maintain. This includes creating columns that flag records based on business rules (such as thresholds, categories, or date ranges), deriving new classifications from existing fields, and automatically segmenting data without manual intervention. You’ll also learn how to generate index columns to create unique row identifiers, support sorting and ordering, manage de-duplication workflows, and prepare tables for more advanced time-intelligence or row-based calculations in later analytical stages.

Throughout the lesson, you’ll work with **Excel’s Power Query Editor** to create, configure, and modify both conditional and index columns, while gaining a better understanding of the underlying M code that gets generated. You will see how these new columns interact with **Power Pivot** data models and how they can ultimately support more advanced **DAX** measures by providing cleaner, structured input data. The focus is on practical application: transforming raw, messy sources into structured datasets that are ready for aggregation, visualization, and modeling.

This lecture is designed for analysts, business users, data professionals, and Excel power users who already have a basic grasp of query-based data importing and want to upgrade their workflows from manual formulas to automated transformations. It is particularly useful if you routinely prepare recurring reports, manage large or complex data sources, or are transitioning from traditional spreadsheet methods to a more model-driven, scalable approach to Excel-based analytics.

In this lesson you’ll walk step by step through the process of turning on the Power Pivot add-in in Excel so you can start building professional data models instead of relying only on standard worksheets and formulas. By the end, you’ll know exactly where to find the Power Pivot settings in different Excel versions, how to activate the add-in, and how to verify that it’s correctly installed and ready to use from the ribbon.

You’ll learn how to troubleshoot the most common setup issues, such as when the Power Pivot tab doesn’t appear, when certain buttons are greyed out, or when your version of Excel doesn’t seem to support the add-in. You’ll understand the prerequisites and licensing considerations so you can determine whether your current Excel installation is compatible and what options you have if it isn’t. After completing this session, you’ll be able to confidently configure your Excel environment so it’s fully prepared for data modeling work, including creating relationships, building measures, and working with large datasets.

The core technology highlighted in this lesson is the Power Pivot add-in within Microsoft Excel on Windows desktop. You’ll see how Power Pivot integrates with the Excel interface and prepares the ground for more advanced modeling with Power Query and DAX later in the program. The focus is entirely on practical, in-application steps, using the live Excel interface to demonstrate every action you’ll need to take.

This lesson is aimed at Excel users who are ready to move beyond basic spreadsheets and pivot tables into more robust analytics. It’s ideal for analysts, finance professionals, business intelligence enthusiasts, data-driven managers, and anyone following the program who wants to build solid foundations for data modeling. Whether you are upgrading your skills from intermediate Excel or you’re an experienced user new to the Power Pivot environment, this session ensures your setup is correct so you can fully benefit from the advanced features introduced in subsequent lectures.

In this lesson, learners dive into the core concepts behind building a robust data foundation in Excel using Power Pivot. By the end of the session, they will understand what a data model is, why it’s so powerful compared to traditional flat spreadsheets, and how it underpins advanced analysis with DAX formulas and pivot-based reporting.

Learners will be able to identify the different types of tables in a model (such as lookup/dimension and fact tables), distinguish between star and snowflake schemas at a practical level, and recognize good versus poor modeling practices inside Excel. They will learn how to connect multiple tables through relationships, how relationships replace the need for repeated VLOOKUPs, and how this approach improves performance, scalability, and accuracy in complex workbooks. The lesson also covers one-to-many and many-to-one relationships, active vs. inactive relationships, and the implications these have on calculations and pivot table results.

The main technologies demonstrated in this session are the Excel Power Pivot add-in and the Data Model environment within Excel. Learners will see how to inspect the model view, how tables are linked through keys, and how those relationships are subsequently used in PivotTables and DAX measures. While the focus is conceptual, all explanations are grounded in the actual user interface and features available in modern versions of Excel that support the integrated Data Model and Power Pivot engine.

This lesson is intended for professionals and students who already work with Excel and want to move beyond traditional formulas and single-sheet analysis. It is especially valuable for analysts, finance and accounting professionals, business intelligence beginners, data-savvy managers, and anyone preparing to use DAX for more advanced reporting. Learners should be comfortable with basic pivot tables and formulas, but they do not need prior experience with Power Pivot or data modeling terminology; the session is designed to bridge that gap and prepare them for more advanced modeling and calculation topics that follow.

In this lesson on creating relationships within Power Pivot, learners explore how to connect multiple tables into a coherent, analytical data model that can power sophisticated Excel reporting.

By the end of this lesson, you will be able to:
- Understand the concept of relational data modeling and why it matters for scalable Excel analysis.
- Identify primary keys and foreign keys in your tables and use them correctly when building relationships.
- Create, edit, activate, and delete relationships in the Power Pivot window.
- Set up one-to-many and many-to-one relationships and recognize when a relationship is ambiguous or invalid.
- Manage relationship directions and understand the impact on calculations and filter propagation.
- Diagnose and resolve common relationship errors, such as duplicate keys, mismatched data types, and inactive relationships.
- Prepare your data model so that measures and calculations behave correctly across multiple related tables in later DAX work.

This lesson uses:
- Power Pivot in Excel (data model view and diagram view).
- Excel tables as the underlying data source for the model.
- The Data Model environment for connecting multiple sources.

The content is designed for:
- Analysts, finance professionals, and business users who want to move beyond flat spreadsheets and build robust, multi-table models in Excel.
- Excel users who already work with large or multiple datasets and need clean, reliable relationships between tables for accurate reporting.
- Learners preparing to use more advanced DAX measures and calculations and who need a solid foundation in data modeling concepts.

In this lesson on **Cardinality and Cross Filter Direction**, learners dive deeper into building robust, reliable data models using Power Pivot. By the end of the session, you’ll clearly understand how relationships between tables actually work “under the hood” and how they affect every measure, calculated column and PivotTable you build.

**What you’ll learn and be able to do by the end of this lesson**

- Distinguish between the main types of cardinality in a data model:
- One-to-many
- Many-to-one
- Many-to-many
- Identify the appropriate cardinality for common business scenarios (e.g., Sales ↔ Customers, Sales ↔ Products, Budget ↔ Actuals).
- Understand how incorrect cardinality leads to duplicated values, incorrect totals or empty results in PivotTables and DAX measures.
- Explain the concept of cross filter direction in relationships:
- Single-direction filtering
- Bi-directional filtering (both directions)
- Configure and adjust cross filter directions in your relationships to:
- Control how filters propagate across tables
- Avoid ambiguous or circular relationships
- Ensure that slicers and filters behave as expected.
- Diagnose and fix issues in a data model caused by:
- Wrong relationship direction
- Inappropriate use of bi-directional relationships
- Ambiguous joins in more complex models.
- Apply best practices for modeling:
- When to rely on single-direction filters
- When (and when not) to use bi-directional filters
- How to structure dimension and fact tables to minimize relationship problems.
- Validate your model by testing filters and relationships in PivotTables and DAX measures to confirm that the data model returns accurate, non-duplicated results.

**Tools and technologies used in this lesson**

This lesson is hands-on and focuses on:

- **Power Pivot** in Excel for:
- Creating and editing relationships
- Setting cardinality and cross filter direction options
- Building a proper star-schema style data model.
- **Data Model view** in Excel for:
- Visualizing table relationships
- Inspecting cardinality between tables.
- **DAX (Data Analysis Expressions)** to:
- Test how filters propagate with different relationship settings
- Demonstrate the impact of cardinality and cross filter direction on measures and totals.

Excel’s built-in Data Model and the Power Pivot window are used throughout to demonstrate every concept with practical examples.

**Who this lesson is for**

This lesson is designed for learners who:

- Already know basic Excel formulas and PivotTables, and want to move into professional-level analytics and modeling.
- Are using (or planning to use) Power Pivot and DAX for reporting, dashboards, and self-service BI.
- Need to combine multiple tables (Sales, Customers, Products, Dates, Budgets, etc.) into a single, coherent model for analysis.
- Work as data analysts, business analysts, finance professionals, BI professionals, or power users responsible for building or maintaining Excel-based reporting systems.
- Have struggled with:
- Wrong totals in PivotTables
- Unexpected filter behavior
- Confusing relationships between tables
and want a clear understanding of how to set up cardinality and cross filter directions correctly.

By completing this lesson, you’ll be able to design cleaner data models in Excel, configure relationships with confidence, and avoid many of the hidden pitfalls that cause incorrect analytics in Power Pivot and DAX-driven reports.

In this lesson on Fact and Dimensions Tables within the broader data modeling and Power Pivot workflow, learners will build a practical understanding of how to structure data for robust analysis and reporting.

By the end of this lesson, learners will be able to:
- Clearly distinguish between fact tables (transactional, numeric data) and dimension tables (descriptive, categorical data).
- Identify which tables in a raw dataset should serve as facts vs. dimensions based on business processes and reporting needs.
- Design a simple star schema or snowflake schema suitable for use in analytical models.
- Normalize messy source data into a clean set of fact and dimension tables that support efficient filtering, slicing and aggregation.
- Recognize and handle common data modeling issues such as duplicate keys, missing keys, and unclear grain (level of detail).
- Prepare their model so that downstream measures and calculations (e.g., with DAX) will be accurate and performant.

Tools and technologies used in this lesson:
- Power Pivot: to create and manage data models, define relationships between fact and dimension tables, and inspect the diagram view.
- Excel Data Model: as the underlying storage of tables and relationships used in analysis.
- Basic use of Power Query (referenced as needed): for shaping and loading source data into separate fact and dimension tables before they are added to the Data Model.

This lesson is intended for:
- Intermediate Excel users who already work with large datasets and pivot tables and want to move into more professional data modeling.
- Business analysts and power users who need to build reusable reporting models rather than ad-hoc spreadsheets.
- Finance, operations, sales, marketing and reporting professionals who handle multi-table data (e.g., transactions, customers, products, calendar tables) and want to improve the structure and reliability of their analysis.
- Anyone preparing to use DAX measures and advanced calculations and needing a solid foundation in how fact and dimension tables should be set up to avoid logic errors and inconsistent results.

In this lesson on Star and Snowflake Schemas within Power Pivot and data modeling, learners will move beyond basic relationships and gain a clear understanding of how to design robust, scalable data models in Excel. By the end of the session, participants will be able to distinguish between star and snowflake schema designs, identify when to use each, and restructure messy, flat data into clean, relational models that are easier to analyze and maintain. They will learn how to translate business requirements into dimension and fact tables, build relationship diagrams that optimize reporting performance, and avoid common modeling errors that lead to wrong or duplicated figures in pivot tables and DAX measures.

The lesson focuses on using Power Pivot inside Excel as the primary tool for constructing and visualizing the schema. Learners will work within the Power Pivot data model view to set up tables, define relationships, and visually confirm the structure of their star or snowflake schema. The concepts taught are aligned with how Power Query prepares source tables and how DAX interacts with dimension and fact tables, so learners see how the model structure directly affects calculations, filters, and aggregations in advanced reports and dashboards.

This content is designed for learners who already know how to use basic Excel formulas and pivot tables and want to advance into more professional data modeling practices. It is particularly relevant for analysts, business intelligence professionals, financial modellers, data‑driven managers, and Excel power users who need to handle larger, more complex datasets and produce reliable, repeatable analysis. Anyone transitioning from simple spreadsheets to structured analytical models in Excel will benefit from understanding star and snowflake schemas in the context of the Power Pivot data model.

In this lesson on using Pivot Tables for data analysis, learners will move from simply organizing data to extracting meaningful insights quickly and confidently. By the end of the session, they will be able to:

- Build Pivot Tables from raw datasets to summarize large volumes of information in seconds.
- Rearrange fields (rows, columns, values, filters) to answer different business questions without rewriting formulas.
- Apply grouping, sorting, and filtering to highlight key trends, exceptions, and performance drivers.
- Use calculated fields and basic aggregations (sum, count, average, max, min) to perform deeper analysis directly inside a Pivot Table.
- Format and customize Pivot Tables to create clear, presentation-ready summary reports.
- Connect Pivot Tables to underlying data models so that updates to the source data are reflected instantly in the analysis.

The lesson focuses on built‑in features of Microsoft Excel, with an emphasis on Pivot Tables and related tools such as:

- Excel Pivot Tables (core engine for summarizing and slicing data).
- Pivot Charts for turning summaries into visuals.
- Basic integration with the data model created through modern Excel capabilities, so the summarized views are consistent with more advanced models.

This session is designed for:

- Professionals who work with medium to large datasets and need fast, reliable summaries for decision‑making.
- Analysts, accountants, marketers, operations staff, and managers who want to turn raw data into dashboards and management reports.
- Students and job‑seekers aiming to strengthen their analytical profile with practical, in‑demand spreadsheet skills.
- Intermediate Excel users who are comfortable with basic formulas and tables and are ready to upgrade their analysis workflow with more powerful summarization tools.

In this lesson, you will learn how to transform traditional pivot table summaries into interactive visual dashboards using pivot charts. By the end of the session, you’ll be able to build clear, insight‑rich visualizations directly from your summarized data and use them to explore trends, patterns, and outliers with confidence.

You will practice creating different pivot chart types—such as column, bar, line, and combination charts—directly from existing pivot tables, and understand when to use each type for different business questions. You’ll learn how to link charts to slicers and timelines so you can interactively filter your visuals and instantly see how results change across products, regions, time periods, or other key dimensions. The lesson also covers formatting best practices, including customizing chart layouts, labels, colors, and legends so that your visuals are easy to read and presentation‑ready.

The primary technology used in this lesson is Microsoft Excel, focusing on the built‑in PivotTable and PivotChart features. You will see how pivot charts integrate with other Excel analysis tools you’ve encountered in earlier parts of the program, so your visualizations remain dynamically connected to the underlying model and refresh automatically when data changes.

This lesson is designed for professionals, analysts, managers, and students who already know how to create and manipulate pivot tables and now want to communicate their findings more effectively through charts. It is ideal for anyone who needs to present data‑driven insights to stakeholders, prepare reports or dashboards, or quickly explore summarized data visually without leaving the spreadsheet environment.

In this introductory lesson on DAX, learners explore the core concepts of Data Analysis Expressions and how it fits into the modern Excel data model environment. By the end of the session, participants will understand what DAX is, why it is different from standard Excel formulas, and where it is used within the data modeling workflow. They will be able to recognize typical DAX use cases, such as creating calculated columns, measures, and basic aggregations that enhance reporting and analytics within the data model.

The lesson walks through the fundamental building blocks of DAX syntax, including how expressions are structured and how they interact with tables and relationships created in the data model. Learners will see how DAX leverages row context and filter context at a conceptual level, setting the stage for more advanced calculations in later lessons. Practical examples demonstrate how simple expressions can drive powerful summaries, dynamic calculations, and more insightful dashboards.

This lesson makes use of Excel’s data modeling capabilities, focusing on the integration of DAX inside the Power Pivot environment. Learners will become comfortable navigating the Power Pivot window, locating where to write DAX formulas, and understanding how those formulas flow through PivotTables and other Excel reporting tools. The lesson may also reference how these concepts extend to related technologies such as Power BI, helping learners understand the transferability of their skills.

The content is designed for professionals and students who already have a solid grasp of standard spreadsheets and are beginning to work with more advanced data analysis features. It is particularly suited to analysts, business intelligence practitioners, finance professionals, consultants, and power users who need to move beyond traditional formulas into scalable, model-based analytics. Even if learners are new to DAX, the lesson provides a gentle, structured introduction that prepares them for more complex calculations and functions in later modules.

In this lesson on DAX syntax and the concepts of row and filter context, learners are guided from simply “using formulas” to actually understanding how the DAX engine thinks and evaluates calculations. By the end of the session, participants will be able to confidently read, write, and troubleshoot core DAX expressions used in data models and dashboards.

You will learn how DAX formulas are structured, including the distinction between measures and calculated columns, how arguments are passed to functions, and the role of data types and operator precedence. The lesson walks through practical examples of basic aggregation, logical, and iterator functions to show how the same formula behaves differently depending on context. Special emphasis is placed on the difference between row context (how DAX evaluates expressions one row at a time) and filter context (how filters from slicers, visuals, and relationships shape the result of a calculation). After completing this lecture, you’ll be able to recognize when a calculation returns unexpected results due to context, and you’ll know how to adjust your formulas—using functions like CALCULATE and filter modifiers—to obtain accurate, business-ready measures.

The demonstration uses the DAX formula language as implemented in Power Pivot and Power BI–style tabular models, with hands-on examples inside an Excel data model powered by Power Pivot and linked queries prepared with Power Query. You’ll see where and how to enter DAX expressions in the data model, how DAX interacts with tables and relationships, and how to verify results using PivotTables or similar analytical views.

This lesson is designed for professionals who already manage data in spreadsheets and want to move into true data modeling and analytics. It is particularly relevant for analysts, business intelligence developers, finance and operations professionals, and anyone building interactive reports or dashboards from relational data. Learners should be comfortable with standard spreadsheet formulas and basic data manipulation, and now want to deepen their skills by mastering how DAX evaluates calculations through row and filter context in a modern data model.

In this lesson on DAX within the broader advanced Excel ecosystem, learners dive deep into the different types of operators and functions that power dynamic calculations in data models. By the end of the session, they will be able to confidently build, read, and troubleshoot DAX expressions that leverage arithmetic, comparison, logical, text, and filter-related constructs. They will understand how various operator types interact with each other, how operator precedence affects results, and how to combine multiple functions to create reusable, high‑impact measures and calculated columns for reporting and analytics.

The lesson walks through the most commonly used DAX function categories—such as aggregation, logical, text, date and time, filter, iterator (X) functions, and basic time intelligence—and explains when and why to use each one. Learners will see practical examples of writing measures that respond to filters, slicing and dicing data with conditions, and chaining functions together for more complex calculations, all directly inside data models. This includes writing expressions that can support dashboards, KPI tracking, and ad‑hoc analysis.

The primary tools used in this lesson are the Excel Data Model and the DAX formula engine as accessed through features such as Power Pivot. Learners will work in an environment where DAX expressions drive calculated columns and measures, and they will see how these interact with pivot tables and other analytical views. The focus is not on basic spreadsheet formulas, but on model‑based calculations that underpin serious business intelligence work.

This lesson is designed for intermediate to advanced Excel users who are ready to move beyond standard formulas into data modeling and analytical expressions. It is particularly well suited to business analysts, financial and operations professionals, data‑savvy managers, and anyone who needs to build robust, scalable reporting solutions in Excel using modern BI tools. Participants should already be familiar with pivot tables and the fundamentals of working with structured data; this session then extends those skills into the world of DAX operators and functions for more powerful, model‑driven analysis.

In this lesson on “Demonstration of New Calculated Columns and Measures,” learners deepen their understanding of data modeling with DAX and see how to practically extend data models with both calculated columns and measures.

By the end of this lesson, learners will be able to:
- Distinguish clearly between calculated columns and measures, and know when to use each in a data model.
- Create new calculated columns using DAX to derive additional fields such as conditional flags, categorizations (e.g., bands or tiers), and row-level calculations.
- Build core DAX measures (such as totals, averages, counts, and percentage calculations) that respond dynamically to filters and slicers in PivotTables and reports.
- Apply common DAX functions (e.g., SUM, CALCULATE, DIVIDE, IF, RELATED) to solve realistic reporting and analysis scenarios.
- Reference columns across related tables and leverage relationships in the data model to produce meaningful aggregated results.
- Validate and troubleshoot DAX expressions, interpret error messages, and refine formulas for accurate outputs in pivot reports and dashboards.

Tools and technologies featured in this lesson:
- Excel’s Data Model as the foundation for storing and relating tables.
- Power Pivot, including the calculation area where learners define both calculated columns and measures.
- The DAX formula bar and function library inside the Power Pivot window.
- PivotTables (and, where applicable, PivotCharts) in Excel that consume DAX measures for interactive analysis.

Intended audience for this lesson:
- Intermediate to advanced Excel users who already work with PivotTables and want to move into data modeling and DAX-based analytics.
- Business analysts, financial analysts, and data professionals who need to create reusable, logic-driven metrics instead of manual worksheet calculations.
- Professionals preparing to build more sophisticated reporting models using DAX in Excel before progressing to enterprise-scale tools like Power BI.
- Learners who have followed earlier Power Query and Power Pivot content and now want a practical, example-driven introduction to creating calculated columns and measures with DAX.

In this lesson on using explicit measures to create new measures in DAX, learners move beyond basic calculated fields and begin working with a more scalable, professional approach to building analytical models in Excel. By the end of the session, you will understand the difference between implicit and explicit measures, why explicit measures are essential in serious data models, and how to build, manage, and reuse them effectively in your reports.

You will learn how to:

- Create explicit measures in the Power Pivot data model using DAX formulas.
- Convert common implicit aggregations (such as automatic sums and averages) into robust, reusable explicit measures.
- Build layered calculations by referencing existing measures inside new measures.
- Apply explicit measures in PivotTables, PivotCharts, and Power Pivot reports for cleaner, more flexible analysis.
- Name, format, and organize measures according to best practices so large models remain understandable and maintainable.
- Avoid common pitfalls such as mixing row context and filter context when defining new business metrics.

The lesson is fully hands-on and uses:

- Microsoft Excel’s data modeling capabilities.
- Power Pivot for managing tables, relationships, and measure definitions.
- The DAX (Data Analysis Expressions) language for writing explicit measures.

This content is designed for users who already have a working knowledge of Excel and are starting to explore data modeling and advanced analytics. It is particularly suited to:

- Business analysts and data professionals who need consistent, reliable KPIs across multiple reports.
- Finance, operations, and marketing professionals who want to move beyond basic PivotTable summaries.
- Excel power users transitioning into more advanced BI workflows and preparing to work with tools such as Power BI, but starting within the familiar Excel environment.

By the end of the lecture, you will be able to replace ad hoc calculations with structured explicit measures, laying the groundwork for more powerful DAX-based analytics in your data models.

In this lesson on the IF function in DAX, learners will build a practical foundation for writing conditional logic inside data models. By the end of the session, you will be able to construct DAX formulas using IF to test conditions, return different results based on business rules, and control how measures and calculated columns behave in your reports. You will learn how to structure the IF syntax correctly, avoid common errors, and nest multiple IF statements to handle more complex decision trees. The class will also cover the differences between using IF in calculated columns versus measures, and how context can affect the outcome of those expressions.

This lesson uses Microsoft Excel with the Data Model and Power Pivot add-in enabled. You will work directly inside the Power Pivot window and the DAX formula bar to create calculated columns and measures leveraging the IF function. Where relevant, you’ll see how these DAX-based calculations feed through into PivotTables and PivotCharts, ensuring you understand how your logical expressions impact analysis and reporting.

The content is designed for learners who already have a comfortable grasp of standard Excel formulas and are ready to move into more advanced modeling with DAX. It’s ideal for business analysts, financial analysts, data professionals, and power users who are building robust, refreshable reports and dashboards. If you’re looking to translate everyday Excel logic into scalable DAX calculations for more powerful analytics, this lesson is tailored to your needs.

In this lesson, learners explore two essential DAX aggregation functions—COUNT and DISTINCTCOUNT—and learn how to apply them confidently in real data models. By the end of the session, participants will understand the difference between counting all rows versus counting unique values and will be able to choose the right function for different analytical scenarios.

You will see how to create calculated measures and calculated columns using these functions, and how they behave with various data types (numeric, text, and blank values). The lesson walks through practical business examples such as counting total transactions versus unique customers, evaluating unique product codes, and measuring distinct dates for activity analysis. You will also learn how filter context affects both COUNT and DISTINCTCOUNT, and how they interact with slicers, row and column labels, and other measures in reports. After completing the lesson, you will be able to build and interpret basic DAX measures that rely on counts, avoid common mistakes (like counting blanks or misinterpreting duplicates), and troubleshoot unexpected results when working with summarized data.

The technologies demonstrated here are the DAX formula language within the data modeling environment of Excel’s data model. You will see these functions applied in PivotTables connected to the internal data model, as well as in the Power Pivot interface for creating and editing measures and calculated fields. The focus is on hands-on work directly inside Excel, using the built‑in data modeling and DAX capabilities.

This lesson is designed for learners who already have a foundational understanding of PivotTables and the data model and are now moving into more advanced formula-based analysis. It is ideal for data analysts, business intelligence users, finance professionals, and anyone responsible for reporting or dashboards who needs to understand how to count records and unique items accurately in DAX-driven models. It also suits self-taught Excel power users who want to progress from standard formulas into more robust, model-based analytics.

In this lesson on the RELATED function, learners dive deeper into Data Analysis Expressions (DAX) to unlock the full analytical power of data models in Excel. By the end of the session, they will be able to confidently use RELATED to pull fields from lookup tables into fact tables, create more robust relationships-driven calculations, and build cleaner, more scalable data models. Learners will understand how RELATED works across one-to-many relationships, when it can and cannot be used, and how it differs from concepts like VLOOKUP or INDEX/MATCH. They will also be able to troubleshoot common errors that arise when relationships are missing, ambiguous, or incorrectly defined in the data model.

The lesson is hands-on and uses Excel’s data modeling environment as the primary tool. Learners will work with Power Pivot to define and inspect relationships between tables in the data model. Within that environment, they will write and test DAX formulas that use the RELATED function in both calculated columns and measures. The focus is on integrating RELATED into practical reporting scenarios, showing how it interacts with other DAX functions and how it leverages the relationships created through Power Query and the data model.

This lesson is ideal for Excel users who are already comfortable with formulas and pivot tables and are ready to move into more advanced data modeling. It is particularly relevant to business analysts, financial analysts, data professionals, report developers, and power users who work with multi-table datasets and want to replace manual lookup formulas with relationship-based DAX. Anyone aiming to build dynamic, maintainable reports and dashboards in Excel will benefit from understanding how and when to use RELATED effectively.

In this lesson on the CALCULATE function in DAX, learners explore how to control and refine calculations in data models using Power Pivot and related tools. By the end of the session, they will be able to write and interpret CALCULATE statements, modify filter context intentionally, and use this function as the foundation for more advanced measures in their Excel-based analytics.

Participants will learn how CALCULATE interacts with row and filter context, how it can override or add filters, and how it behaves differently from basic aggregation functions. They will create practical measures such as year‑to‑date and conditional aggregations (e.g., sales for a specific product category or region), and they will gain the confidence to troubleshoot unexpected results in their measures by understanding how context transition works. The lesson also covers combining CALCULATE with common DAX helpers like FILTER, ALL, and ALLEXCEPT to build robust, reusable business metrics.

This lesson uses Microsoft Excel’s data modeling capabilities, primarily focusing on Power Pivot and the DAX formula language within the Excel environment. Learners will work inside the Power Pivot window and the measure grid, applying DAX formulas to real tables and relationships, and then testing their results with PivotTables connected to the data model.

The content is designed for intermediate to advanced Excel users who are starting to work with data models and DAX, including analysts, business intelligence professionals, and power users who need to move beyond standard formulas and PivotTables. It is especially valuable for those who already know basic measures and relationships in Excel’s data model and want to deepen their understanding of how to build flexible, context-aware calculations that drive dashboards and reports.