The Complete Excel Course with AI Integration

Automate a sales report in Excel with Power Query

SalesFusion is the sales report you build once in The Complete Excel Course with AI Integration and never rebuild by hand again: an industry-standard project designed after talking with people from many different industries. Power Query cleans messy CSV exports in recorded steps, adds product and region names, stacks the years and takes in a whole folder of files. A Power Pivot data model and DAX measures feed four PivotTables with slicers, and next month's file is one refresh away from the report.

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:

  • Import a CSV with Power Query and clean it in recorded, named steps: tidy headers, trimmed codes, symbols and NA entries stripped, words turned into numbers and every data type set.
  • Add the columns a sales report needs inside the query: year, month name, month number and year-month, then profit, margin, a high-value flag and a return flag.
  • Prove the data before reporting on it with a Group By check by month and region, and keep staging queries as connections only so the workbook stays lean.
  • Merge product and region names into every sale with a left outer join, reuse the whole pipeline on the next year's file and append the years into one table.
  • Combine every file in a folder so a new export joins on the next refresh, and unpivot month-per-column exports into rows that survive new months.
  • Relate the tables in a Power Pivot data model, write DAX measures for sales, profit, orders, margin %, average order value and share of total, and report them by category, region, month and top 10 products.

Projects

What you will build.

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

  • Project 12

    SalesFusion

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

    The report on this page, built from a dataset of messy CSV files (sales by year, a folder of period files, product and region lookups, a wide export) to a finished workbook you can compare yours with.

  • Project 05

    SalesPlusPerformance

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

    Where PivotTables are learnt on order-level sales data: the first one built, fields arranged, dates grouped, layouts and number formats set and Refresh All pressed, all of which SalesFusion's report relies on.

  • Project 13

    AutoFlow

    The weekly report routine, run by a flow instead of by you.

    Power Automate Desktop flows, on Windows, that launch Excel, read a sheet, decide with if conditions, loop over rows and write results back, run from a shortcut or a link: for the routine around the report.

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 27 of them, 2 h 27 min of video. Each lesson keeps its number in the course.

Where to start

Lesson 153 SalesFusion Power Query project

Start at Lesson 153 if PivotTables are familiar: download the SalesFusion dataset of CSV files and keep it in one project folder, because the queries point at it. New to PivotTables? Begin at Lesson 57, since the finished report is made of them, or at Lesson 1 if Excel itself is new.

27 of 186 lessons · 2 h 27 min

07 PivotTables and PivotCharts: data analysis made easy 1 of 10 lessons · 5 min A sales dataset read column by column (order, date, region, channel, segment, product, net sales, cost and profit) and what a PivotTable does with thousands of such rows.
  1. 57 PivotTables 101 in Excel What a PivotTable does and the sales dataset 5 min
07 PivotTables and PivotCharts: data analysis made easy 3 of 10 lessons · 7 min Inserting a PivotTable, arranging regions, states and channels in rows and columns around net sales, changing how values are summed, and letting order dates group themselves into years, quarters and months.
  1. 59 Create your first PivotTable in Excel Insert a PivotTable from a sales table 1 min
  2. 60 PivotTable fields in Excel Rows, values, columns and Value Field Settings 2 min
  3. 61 PivotTable summaries in Excel Sales by product category and grouping by date 4 min
07 PivotTables and PivotCharts: data analysis made easy 2 of 10 lessons · 7 min The Design tab (subtotals, grand totals, compact and outline layouts, banded rows, styles and a custom number format for millions), then Refresh and Refresh All after the data changes.
  1. 64 PivotTable design Tab Report layouts, PivotTable styles and number formats 5 min
  2. 65 Refresh a PivotTable in Excel Update pivots when source data changes 2 min
15 SalesFusion: advanced data tools and a Power Query modelling system 21 lessons · 2 h 7 min SalesFusion from raw exports to a report that rebuilds itself: CSV import and recorded cleaning steps, date and profit columns, a QA query, connection-only loads, merges with the lookups, a pipeline reused on the next year and appended, a whole folder combined, a sales fact table, the Power Pivot data model and DAX measures (Excel for Windows), the report and its slicers, and Unpivot for month-per-column exports.
  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
  3. 155 Import a CSV with Power Query Get data from text and transform data 2 min
  4. 156 Clean columns in Power Query Remove and rename columns for modelling 5 min
  5. 157 Clean text in Power Query Trim, clean, replace values and fix text case 6 min
  6. 158 Data types in Power Query Fix date, whole number, decimal and text types 7 min
  7. 159 Date columns in Power Query Add year, month and year-month fields for reports 7 min
  8. 160 Business logic in Power Query Custom and conditional columns for profitability 6 min
  9. 161 Group By in Power Query Summarise sales by month and region to validate data 5 min
  10. 162 Close and Load in Power Query Connection only, table or Data Model 5 min
  11. 163 Merge queries in Power Query Look up product and region names with a left join 5 min
  12. 164 Reuse a Power Query pipeline Duplicate queries for a new year of sales data 5 min
  13. 165 Append queries in Power Query Combine 2024 and 2025 sales tables 3 min
  14. 166 From Folder in Power Query Combine every sales file in a folder automatically 9 min
  15. 167 From Folder automation in Power Query Profit metrics and fiscal year columns 7 min
  16. 168 Build a sales fact table in Power Query Merge lookups and append history 6 min
  17. 169 Power Pivot Data Model in Excel Relationships and Diagram View 6 min
  18. 170 DAX measures in Power Pivot Total sales, margin percent and percent of total 10 min
  19. 171 PivotTable report from the Data Model Format the SalesFusion report in Excel 5 min
  20. 172 Interactive Excel dashboard Data model PivotTables, slicers and styling 10 min
  21. 173 Unpivot columns in Power Query Turn wide month columns into a long table 8 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.

Which Excel course teaches you to build a sales report that updates itself?

The Complete Excel Course with AI Integration from Coding Liquids: SalesFusion, its industry-standard Power Query project, is a sales report built on messy CSV exports that rebuilds itself on every refresh. You record the cleaning once, merge in product and region names, stack the years, combine a whole folder of files, model it all in Power Pivot with DAX measures (those lessons need Excel for Windows), and report sales by category, region, month and top product behind four slicers. That is data analysis in Excel end to end, from raw data to a dashboard, and this page maps it in 27 lessons, 2 hours 27 minutes of video.

How do I combine all the files in a folder with Power Query?

Get Data, From File, From Folder, then Combine and Transform Data, as The Complete Excel Course with AI Integration does with SalesFusion's folder of period files (Lesson 166). Power Query stacks every file into one table, and a Source.Name column records which file each row came from. Do the cleaning in the Transform Sample File query: each step there, from renamed headers to stripped symbols and fixed data types, runs on every file in the folder, today's and the ones you add later. Set column profiling to the entire dataset, and Column Quality and Column Profile show where errors and blanks still hide.

How do I automate a weekly or monthly report in Excel?

Record the work once and let a refresh replay it. In The Complete Excel Course with AI Integration, every cleaning step of SalesFusion is kept in Power Query's Applied Steps, a recipe rerun on each refresh (Lesson 156); the folder query takes in each new export the next time you refresh (Lesson 166); and DAX measures recalculate for whatever the data and slicers hold (Lesson 170). Next month is then: save the export into the folder, press Refresh All, read the report. For the routine around it, such as opening workbooks and writing results back, the AutoFlow project builds Power Automate Desktop flows, on Windows.

How do I make a sales report in Excel?

Write the measures first, then lay the report out around them. The Complete Excel Course with AI Integration builds SalesFusion's report from four PivotTables on the data model: sales by category with each category's share of the total, sales by region with profit and margin %, a monthly trend grouped by year, and the top 10 products through a Value Filter (Lesson 172). Tabular layout, grand totals switched off and two-decimal percentages make it read like a report (Lesson 171), and category, channel, year and region slicers, each connected to all four PivotTables, filter the whole page in one click.

How do I clean messy CSV data with Power Query?

Step by step, with every step named so the cleaning can be audited and replayed. In The Complete Excel Course with AI Integration, SalesFusion's raw export has its codes trimmed and set to upper case so North and NORTH stop counting as two regions, money symbols, commas, hyphens and NA entries removed with Replace Values, quantities typed as words turned into numbers and every column given its proper data type (Lesson 157, Lesson 158). One trap gets its own moment: Replace Errors fixes real errors, not cells holding the word error. Dates lose placeholder text and an impossible day before becoming true dates (Lesson 159).

Do I need Power BI to build an automated sales report?

No. The Complete Excel Course with AI Integration builds the whole of SalesFusion inside Excel: Power Query extracts, transforms and loads the data, Power Pivot relates the sales table to its product and region lookups as one-to-many links drawn in Diagram View (Lesson 169), and DAX measures do the sums: total sales and profit, orders as a distinct count of order IDs, and margin %, average order value and share of total written with DIVIDE, so an empty filter returns a blank, not an error (Lesson 170). Power Pivot needs Excel for Windows. These are the very skills Power BI uses, so everything you learn here moves with you.

What if my export adds a new column every month?

Unpivot it, and new months stop breaking anything. The Complete Excel Course with AI Integration takes a wide export with one column per month, keeps the region, category and metric columns fixed and chooses Unpivot Other Columns, so every month column, including ones that do not exist yet, becomes rows of year-month and amount (Lesson 173). A custom column turns year-month into a real date, Pivot Column gives budget sales and target units a column each, and Group By sums them by month. Appends line columns up by their headers (Lesson 165), so the exports you combine should share them; the folder query's sample file then gives every file the same renamed, cleaned columns.

How do I check that an automated report's totals are right?

Build the check into the pipeline. The Complete Excel Course with AI Integration references SalesFusion's staging query and groups it by year-month and region, with total sales and a row count for each pair (Lesson 161): sorted largest first, a total too good to be true shows at once, a filter drops empty groups and a null month points to dates the cleaning could not read. The check refreshes with the data, and after the merges and the append, filters on product ID, sales amount and year hunt for blanks (Lesson 168). With the instructor support plan, a question on your own query goes to the instructor for two months from the day you switch it on.

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.