The Complete Excel Course with AI Integration

Power Query or Power Pivot: what each one does

Power Query cleans the data, Power Pivot connects it: that is the rule Lesson 169 gives, and The Complete Excel Course with AI Integration has you put it to work with your own hands on SalesFusion, an industry-standard project. Queries trim, merge and append raw sales files and wait in the background as connections; then a data model relates the tables, DAX measures do the sums and a PivotTable report draws on the model. Each lesson picked here is a point where one tool hands over to the other.

Taught by Sagnik Bhattacharya and Virginia Thorn

The Complete Excel Course with AI Integration: Sagnik Bhattacharya and Virginia Thorn

What you will be able to do

You will be able to:

  • Tell at a glance whether a task belongs in Power Query, where data is cleaned, combined and reshaped, or in Power Pivot, where tables are related and measured.
  • Load staging queries as connections only and send a finished table to the data model, so the workbook stays lean.
  • Merge product and region names into a sales fact table with a left outer join, append the history and hunt down blanks before modelling.
  • Relate a fact table to its lookup tables one-to-many in Power Pivot's Diagram View.
  • Write DAX measures for total sales, margin and share of total, then lay them out in a PivotTable report drawn from the data model.

Projects

What you will build.

The course has 14 industry-standard projects. These two matter most here.

  • Project 12

    SalesFusion

    A sales report that refreshes itself from a folder of files.

    Both tools on one dataset: Power Query turns raw CSV exports, a folder of period files and two lookup tables into clean queries, then Power Pivot relates them and measures them with DAX for the report.

  • Project 05

    SalesPlusPerformance

    A sales question answered in seconds, straight from the order data.

    The everyday PivotTable on a single sales table, fields dragged into rows and values: the baseline that shows what a PivotTable drawn from the data model adds.

Tap or pinch to zoom in, drag to move around. Click or scroll to zoom in, drag to move around.

Lessons

The lessons that matter, in course order.

The Complete Excel Course with AI Integration has 186 lessons. This page picks out 7 of them, 42 min of video. Each lesson keeps its number in the course.

Where to start

Lesson 153 SalesFusion Power Query project

Start at Lesson 153: it sets out what each tool is for, and the SalesFusion dataset of CSV files comes with its resources. This path skips the cleaning lessons in between, so to build the model on queries of your own, take every lesson from Lesson 155 to Lesson 171 in order. New to PivotTables? Begin at Lesson 57.

7 of 186 lessons · 42 min

15 SalesFusion: advanced data tools and a Power Query modelling system 2 of 21 lessons · 11 min Where the two jobs are first told apart: Power Query as the engine that turns messy files into clean tables through saved steps, the data model as the map of how those tables relate, and ETL, the extract, transform and load pipeline that feeds them both.
  1. 153 Start here SalesFusion Power Query project Excel ETL pipeline and data model overview 5 min
  2. 154 Introduction to Power Query in Excel The ETL workflow explained 5 min
15 SalesFusion: advanced data tools and a Power Query modelling system 1 of 21 lessons · 5 min The hand-over point: Close and Load with staging queries kept as connections only, so clean data waits in the background for a PivotTable or the data model instead of filling up sheets.
  1. 162 Close and Load in Power Query Connection only, table or Data Model 5 min
15 SalesFusion: advanced data tools and a Power Query modelling system 4 of 21 lessons · 27 min Power Query's last job and Power Pivot's first: a sales fact table merged with its lookups, appended to the history and checked for blanks; then three tables added to the data model and related in Diagram View, DAX measures from total sales to share of total, and a PivotTable report drawn from the model. Power Pivot needs Excel for Windows.
  1. 168 Build a sales fact table in Power Query Merge lookups and append history 6 min
  2. 169 Power Pivot Data Model in Excel Relationships and Diagram View 6 min
  3. 170 DAX measures in Power Pivot Total sales, margin percent and percent of total 10 min
  4. 171 PivotTable report from the Data Model Format the SalesFusion report in Excel 5 min

Instructors

Who teaches it.

Virginia Thorn smiling at the camera, dark hair tied back, wearing a navy top.

Instructor, Excel with AI

Virginia Thorn

Emmy Award winner. Teaches Excel with AI at Coding Liquids.

Questions

Asked before starting.

What is the difference between Power Query and Power Pivot?

Power Query prepares data: it imports, cleans, merges, appends and reshapes, and saves every step to replay on refresh. Power Pivot models it: it holds the cleaned tables in the data model, relates them by their keys and measures them with DAX. Reach for Power Query when the data is messy or scattered, for Power Pivot when clean tables must answer questions together, and for both, in that order, when a report draws on several tables. The Complete Excel Course with AI Integration from Coding Liquids teaches the pair on SalesFusion, and the 7 lessons picked here (42 minutes) show where one ends and the other begins.

What is Power Pivot in Excel?

Excel's data modelling tool: the place where several tables become one model you can report from. The Complete Excel Course with AI Integration opens it from the Data Model button on the Data tab, with Manage Data Model, then switches to Diagram View to draw one-to-many relationships between a sales table and its region and product lookups (Lesson 169); DAX measures are then written from its own ribbon tab (Lesson 170). The course's Power Pivot lessons need Excel for Windows.

Is Power Pivot the same as a PivotTable?

They work as a pair: the PivotTable is the report, Power Pivot the model behind it. A plain PivotTable sums the columns of one table you drag into it. One inserted From Data Model, as The Complete Excel Course with AI Integration does it (Lesson 170), shows measures instead: orders counted with DISTINCTCOUNT, so an order of several products counts once, and each category's share of total sales, both worked out afresh for every filter and slicer (Lesson 171). That is what Power Pivot adds to the PivotTable you already know.

Should I learn Power Query or Power Pivot first?

Power Query, because a model is only as sound as the data poured into it. The Complete Excel Course with AI Integration keeps SalesFusion in Power Query from Lesson 154 to Lesson 168 and turns to Power Pivot only once the sales fact table is merged, appended and checked. The DAX lesson even shows why: a total sales measure fails because the amounts arrived as text, and the fix is made back in Power Query by setting the column's data type to a decimal number (Lesson 170). Learn them in that order and every measure stands on clean ground.

Should I merge tables in Power Query or relate them in Power Pivot?

Merge when you want the matching columns inside the table itself; relate when the tables should stay apart and work together. A merge in Power Query is a lookup done at refresh: SalesFusion's fact table takes in its product and region names that way (Lesson 168). A relationship in Power Pivot keeps each product once in its own table, linked to its many sales by its product ID (Lesson 169), the structured, easy-to-maintain design argued for in Lesson 153. The Complete Excel Course with AI Integration teaches both, so the choice is yours on every workbook you build.

Is DAX part of Power Query or Power Pivot?

Power Pivot. In The Complete Excel Course with AI Integration DAX measures are created from the Measures drop-down on the Power Pivot tab and live in the data model (Lesson 170). Power Query has its own formula language, M, which SalesFusion types into custom columns such as profit and margin (Lesson 167). The difference matters for your numbers: a Power Query column is worked out row by row each time the data refreshes, while a DAX measure is worked out afresh for whatever the PivotTable and its slicers show.

Where does Power BI fit next to Power Query and Power Pivot?

Power BI Desktop carries both inside it: the same Power Query editor for preparing data, and a model with relationships and DAX measures doing the job Power Pivot does in Excel, plus a canvas of visuals and a service for sharing reports. So the pair you learn in The Complete Excel Course with AI Integration is Power BI's back end, learnt inside Excel on SalesFusion's data model (Lesson 169) and its measures (Lesson 170). Power BI's canvas and sharing are not in the lessons; its models and measures will feel familiar from the first query.

Which course explains when to use Power Query and when to use Power Pivot?

The Complete Excel Course with AI Integration does, by having you use each where it belongs on SalesFusion, an industry-standard project designed after talking with people from many different industries. Power Query cleans, merges and appends the sales files and loads them as connections (Lesson 162); Power Pivot then relates the tables, and DAX measures turn them into a report (Lesson 171). The finished workbook is there to compare yours with, lifetime access keeps every lesson open for the next model you build, and with the instructor support plan, an option you switch on when you choose, your written questions on any lesson go to the instructor for two months, with Ask AI alongside in every lesson.

Excel with AI

See the whole course.

The Complete Excel Course with AI Integration: 16 hours of video in 186 lessons, built around 14 industry-standard projects, with lifetime access, a Certificate of Completion and a 14-day money-back guarantee.

The same course

For another job, goal, level, topic or comparison.