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
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.
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 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.
- 153 Start here SalesFusion Power Query project Excel ETL pipeline and data model overview 5 min
- 154 Introduction to Power Query in Excel The ETL workflow explained 5 min
15 SalesFusion: advanced data tools and a Power Query modelling system 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.
- 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 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.
- 168 Build a sales fact table in Power Query Merge lookups and append history 6 min
- 169 Power Pivot Data Model in Excel Relationships and Diagram View 6 min
- 170 DAX measures in Power Pivot Total sales, margin percent and percent of total 10 min
- 171 PivotTable report from the Data Model Format the SalesFusion report in Excel 5 min
Instructors
Who teaches it.
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.
By job
Finance and accounting
- Accountants and finance teams
- Bookkeepers and accounts assistants
- FP&A and budget analysts
- Auditors and fraud examiners
- Payroll administrators
- Tax accountants and advisers
- Banking professionals
- Cost and management accountants
- Financial controllers
- Credit control and receivables teams
- Accounts payable teams
- Financial planners and advisers
- Credit analysts and loan officers
- Insurance underwriters
- Insurance claims handlers
- Reconciliation analysts
- Treasury analysts
- Medical billing and coding specialists
- Fund accountants
- Billing administrators
- AML and KYC analysts
- Actuarial analysts
- Insurance agents and brokers
- Retirement planners
HR and office administration
Operations and supply chain
- Operations and project managers
- Procurement and purchasing officers
- Logistics coordinators
- Inventory controllers and storekeepers
- Supply chain analysts
- Warehouse managers and supervisors
- PMO analysts
- Import and export coordinators
- Production planners
- Demand planners
- Fleet and transport managers
- Apparel merchandisers
Data and reporting
Sales, marketing and customer service
Business and management
Engineering, construction and manufacturing
- Quantity surveyors and estimators
- Civil and site engineers
- Quality managers and engineers
- Construction project managers
- Health and safety officers
- Document controllers
- Production managers and supervisors
- Mechanical and electrical engineers
- Industrial engineers
- Planning engineers
- Sustainability and ESG analysts
- Maintenance planners
Retail, property and hospitality
Healthcare and life sciences
Education and careers
Public sector, non-profit and legal
By goal
- Instructor support
- Certificates
- How long it takes
- Is Excel worth learning
- A retirement calculator
- A budget tracker
- A task tracker
- A KPI dashboard
- A budget vs actual report
- A sales goal tracker
- An employee database
- A chart dashboard
- An employee lookup
- A self-refreshing sales report
- A checking macro
- A Power Automate flow
- A compound interest calculator
- A SIP calculator
- An Excel test for a job
- A step-up SIP calculator
- Practice exercises
- Excel skills for a CV