[Integrated Practice]Sales Data → Analysis → Report End-to-End

Summary: From a rough sales Excel file → Power Query cleanup → Power BI modeling and DAX analysis → export charts and data → AI one-click generates a report PPT. The whole pipeline runs in about 30 minutes, with DAX formulas that are real and copy-paste ready.

1. The Pain Point

Monday, 9 a.m. Your boss drops a line in the group chat: "How did the East China region sales go last week? I need a report by Friday."

You open the Excel files on your desk. The scene looks roughly like this:

An Sales_Detail_2025.xlsx exported straight from your ERP—30,000 rows, with column names in cryptic English abbreviations (cust_id, prod_sku, amt, region_cd), plus piles of blank rows, merged cells, and dates written in two formats: 2025/10/1 and 2025-10-01.
A Product_Master.xlsx and a Customer_Master.xlsx, which join to the sales detail by Product ID and Customer ID—you have to VLOOKUP them by hand.
What the boss actually wants: total sales, year-over-year growth, Top 10 products, and regional share—stuffed into a PPT at the end.

If you do it all by hand, the script looks like this: spend 30 minutes cleaning data in Excel (drop blank rows, fix formats, fill columns) → spend 1 hour building PivotTables (one pass per dimension: product, region, time) → manually copy PivotTable data into PPT, drop a column chart on each page, tweak the colors → at least 3–4 hours for a "looks decent" report. The worse pain: every time the data refreshes, every step has to be redone.

This article compresses the entire pipeline to about 30 minutes, and when the data refreshes, you only need to hit Refresh—almost no rework. We will chain five tools together:

Excel (raw data) → Power Query (data cleanup, the "T" in ETL) → Power BI (modeling + DAX analysis + visualization) → Screenshots / Data export (carry over charts) → AI tool (one-click PPT draft). Power BI is the main battlefield; DAX and Power Query are the hard-core knowledge points.

Key Terms to Know First

The first time each appears, a plain-English explanation is added. After that, no repetition:

Power Query = the "data cleanup pipeline" built into Excel and Power BI. You click through the UI; behind the scenes it is the M language (a SQL-like functional language that describes "where to fetch, how to transform, where to land").
Power BI = Microsoft's business intelligence tool, which handles "modeling + charts + reports" in one flow. It can produce interactive dashboards.
DAX (Data Analysis Expressions) = the formula language used in Power BI. Formulas recalculate automatically as the user filters (e.g., click "show October only" and every number on the dashboard shifts together).
Measure = a DAX formula that produces a "dynamic number" that follows the filter context. It differs from a calculated column, which is computed row by row and stored in the table.
Copilot = Microsoft's AI assistant, integrated into Power BI / PowerPoint / Excel, that can generate drafts from your prompts.

2. Goal Deliverables

After following the steps, you will walk away with four artifacts:

A Power Query cleanup script (.pq or .txt): runs from the raw Excel and does "drop blanks + fix types + merge tables + produce a clean table" in one click. When new weekly data arrives, you only need to refresh once.
A Power BI data model (.pbix): contains 3 tables (Sales Detail, Product, Customer) + 1 Date table, with a clean relationship diagram.
7 core DAX measures: Total Sales, Order Count, Average Order Value, YoY Growth, YTD Sales, Top 10 Rank. All are real and runnable—copy and use.
An AI-generated report PPT (.pptx, 8 pages): Cover + KPI cards + regional breakdown + Top 10 products + trend analysis + conclusion + Q&A.

Prerequisites

Item	Minimum Version	Notes
Excel	2016 / Microsoft 365	Hosts Power Query and is used for exporting data
Power BI Desktop	August 2024 release or later	Modeling, writing DAX, charting; free download from Microsoft
PowerPoint	2019 / Microsoft 365	Hosts Copilot / AI-tool PPT drafts
AI tool	Any	Qwen, Kimi, ChatGPT, ERNIE Bot all work; this article uses Copilot + Qwen
Raw data	3 Excel files	Sales Detail / Product Master / Customer Master

About Copilot's capability limits (important, read first): Starting from 2024, Power BI's Copilot requires a Fabric / Premium capacity (PPU also counts) to unlock the full feature set. The free version of Power BI Desktop cannot use Copilot's auto-write-DAX feature. Every DAX formula in this article is handwritten, does not depend on Copilot, and runs as-is when copied. Copilot for Microsoft 365 in PowerPoint can "generate a PPT draft from a Word document," but whether it can directly read Power BI screenshots and generate a PPT—currently not guaranteed. The safe path is: manually screenshot, then let the AI look at the images and generate.

3. Case Walkthrough

We use a fictional "fast-moving consumer goods (FMCG) company" as the case study and run the entire pipeline. All file names, column names, and data are real and reproducible—just remap the fields to your own data.

Step 1: Prepare the Raw Excel Data (5 minutes)

Open Excel and create 3 files (or use your company's existing data) in the folder C:\Sales_Data\:

Sales_Detail_2025.xlsx (main table, ~30,000 rows)

Column Name (English original)	Plain English	Sample
order_date	Order date	2025-10-15
order_id	Order ID	SO20251015001
cust_id	Customer ID	C0001
prod_sku	Product ID	SKU001
qty	Quantity	10
unit_price	Unit Price	89.5
amt	Sales Amount	895
region_cd	Region Code	EAST

Product_Master.xlsx

prod_sku	product_name	category
SKU001	Mineral Water 550ml	Beverages
SKU002	Peanuts 200g	Snacks

Customer_Master.xlsx

cust_id	cust_name	cust_type
C0001	Shanghai RT-Mart	KA Customer
C0002	Beijing Wumart	KA Customer

Key point: The column names in Sales Detail are deliberately left as cryptic English abbreviations (exactly as the ERP exports them). Power Query will rename them later. This saves roughly half the time compared to "rename them in Excel first and then import"—let Power Query do the renaming.

Step 2: Clean the Data with Power Query (10 minutes)

Open Power BI Desktop → click "Home" on the top ribbon → "Get Data" → "Excel workbook" → select Sales_Detail_2025.xlsx → in the Navigator, check Sales Detail$sheet → click "Transform Data".

This opens the Power Query Editor, which has three panes: the left pane is the query list, the center pane is the data preview, and the right pane is the "Applied Steps" list (each change auto-adds a step).

2.1 Sales Detail Cleanup Steps

Apply the following in order. Each operation auto-generates a row in "Applied Steps" (this is the essence of Power Query—every action becomes a replayable step, and next time new data arrives, just click "Refresh" to rerun them):

Promote header row: Home → Use First Row as Headers (auto-generates #"Promoted Headers")
Change column types: select the three columns qty / unit_price / amt → Transform → Data Type → Decimal Number; select order_date → Data Type → Date
Remove blank rows and error rows: Home → Remove Rows → Remove Empty Rows (auto-generates #"Removed Empty Rows")
Filter dirty data: click the dropdown arrow on the region_cd column → uncheck null and (null)
Rename columns to English: double-click each column name in turn to rename it: order_date → Order Date, cust_id → Customer ID, prod_sku → Product ID, qty → Quantity, unit_price → Unit Price, amt → Sales Amount, region_cd → Region Code, order_id → Order ID

Once done, click "Advanced Editor" in the left pane. You will see the M code Power Query has generated (M language = the scripting language behind Power Query, SQL-like functional style). It looks like this:

powerquery-m
let
    // 1. Read the Sales Detail sheet from the Excel file
    Source = Excel.Workbook(File.Contents("C:\Sales_Data\Sales_Detail_2025.xlsx"), null, true),
    SalesDetail_Sheet = Source{[Item="Sales Detail",Kind="Sheet"]}[Data],

    // 2. Promote first row as headers
    PromotedHeaders = Table.PromoteHeaders(SalesDetail_Sheet, [PromoteAllScalars=true]),

    // 3. Change column types: date, number, text
    ChangedTypes = Table.TransformColumnTypes(PromotedHeaders, {
        {"order_date", Date.Type},
        {"order_id", type text},
        {"cust_id", type text},
        {"prod_sku", type text},
        {"qty", Int64.Type},
        {"unit_price", Number.Type},
        {"amt", Number.Type},
        {"region_cd", type text}
    }),

    // 4. Drop blank rows + filter invalid regions
    RemovedEmptyRows = Table.SelectRows(ChangedTypes, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    FilteredRows = Table.SelectRows(RemovedEmptyRows, each [region_cd] <> null and [region_cd] <> "")
in
    FilteredRows

Key point: You do not have to hand-write this M code—the Power Query editor generates it automatically when you click. But understanding this code matters: when a column name changes or the file path changes, you can edit the code directly—10× faster than clicking. Save this snippet to sales_fact.pq as a backup.

2.2 Product and Customer Cleanup (Simple Version)

Product and Customer master data is usually clean. At most two steps: promote headers + change types. Then Home → Close & Apply to return to the Power BI main window.

2.3 Critical Step: Build Relationships

Back in the Power BI main window, click "Model view" in the left pane (icon: three squares connected by lines). You will see 3 tables. Drag lines to build relationships:

Sales Detail[Product ID] → Product Master[prod_sku] (many-to-one, many = Sales Detail)
Sales Detail[Customer ID] → Customer Master[cust_id] (many-to-one)
Tick the "Single" cross filter direction (filters flow from the "one" side to the "many" side: Master → Sales Detail, i.e., the arrow points toward the fact-table / Sales Detail side)

Pitfall warning: Before building relationships, make sure the join columns have consistent data types (both text). Otherwise the relationship will not build. A solid line means an active relationship (in effect); a dashed line means an inactive one (not in effect — open "Edit relationship" and tick "Make this relationship active"). If you see a dashed line, don't try to use it yet — activate it first.

2.4 Build a Separate "Date Table" (Required)

Time intelligence (YoY, cumulative, year-to-date) requires an independent Date Table—this is the "standard calendar" that DAX time functions rely on. Click Home → Table → New table, then enter:

dax
Date = CALENDAR(DATE(2025,1,1), DATE(2025,12,31))

Then add 3 calculated columns to the Date table:

dax
Year = YEAR('Date'[Date])
Month = MONTH('Date'[Date])
YearMonth = FORMAT('Date'[Date], "YYYY-MM")

Finally, build a relationship between the Date table and Sales Detail: Sales Detail[Order Date] → Date[Date].

Key point: The Date table must be marked as a "date table"—click the Date table → top menu Table tools → Mark as date table → select the Date column. Without marking it, time functions like SAMEPERIODLASTYEAR and TOTALYTD will error out or compute incorrectly.

Step 3: Write the DAX Measures (10 minutes)

Open "Data view" in the left pane of Power BI → select the Sales Detail table → top menu Table tools → New measure. Copy the following 7 measures one by one:

1. Total Sales (The most basic SUM)

dax
Total Sales = SUM('Sales Detail'[Sales Amount])

2. Order Count (Count rows, not a column)

dax
Order Count = COUNTROWS('Sales Detail')

Pitfall: Here we use COUNTROWS instead of COUNT('Sales Detail'[Order ID])—if the Order ID column has blanks, COUNT will undercount. COUNTROWS counts the actual number of rows in the table, and is always accurate.

3. Average Order Value (Sales ÷ Order Count, with DIVIDE to guard against divide-by-zero)

dax
Avg Order Value = DIVIDE([Total Sales], [Order Count], 0)

Plain English: DIVIDE(A, B, fallback) = A ÷ B. If B is 0, it returns the fallback value rather than an error. This is the standard DAX idiom.

4. Year-over-Year Growth Rate (Last year's same-period sales)

dax
Last Year Sales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))

dax
YoY Growth = 
DIVIDE(
    [Total Sales] - [Last Year Sales],
    [Last Year Sales],
    0
)

Key point: SAMEPERIODLASTYEAR is a time intelligence function that shifts the current filter context (e.g., "October") back to October of last year. The prerequisite is that the Date table must be marked as a date table (done in the previous step).

5. Cumulative Sales (Year-to-Date, YTD)

dax
YTD Sales = TOTALYTD([Total Sales], 'Date'[Date])

Plain English: TOTALYTD is DAX's year-to-date time function. It auto-aggregates "all sales from January 1 of the current year up to the date corresponding to the current filter context", and remains correct across year boundaries. The prerequisite is again that the Date table must be marked as a date table and have a relationship with Sales Detail. Note: the alternative form FILTER(ALL('Date'[Date]), 'Date'[Date] <= MAX('Date'[Date])) actually computes "cumulative from the earliest date in the Date table to the current date", which misbehaves at year boundaries—that is why TOTALYTD is recommended. If you cannot refactor, an equivalent form is CALCULATE([Total Sales], DATESYTD('Date'[Date])).

6. Product Sales Rank (Use RANKX to rank products)

dax
Product Sales Rank = 
RANKX(
    ALL('Product Master'[product_name]),
    [Total Sales]
)

Key point: ALL removes filters. Without ALL, product A's rank would be affected by the filter "Product B is currently selected" and always come in 1st. With ALL, no matter how the user filters products, every product is compared against all products, giving stable ranks.

7. (Advanced) Rolling 3-Month Average (For trend analysis)

dax
Rolling 3-Month Avg = 
AVERAGEX(
    DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -3, MONTH),
    [Total Sales]
)

Plain English: DATESINPERIOD returns the date range "3 months back from today", and AVERAGEX computes monthly sales across that range and then averages them. Use a rolling average when monthly volatility is high—reading trends off a rolling average is far more stable than reading raw monthly sales.

After all 7 measures are built, select a measure and use the "Measure tools" ribbon → Format section to set the display format (YoY Growth as a percentage, the others as integer with thousands separator).

Step 4: Build the Visualization Dashboard (10 minutes)

Back in Power BI Report view, drag components from the Visualizations pane on the right:

KPI Cards (key metrics): drag 4 Card visuals, binding [Total Sales], [Order Count], [Avg Order Value], [YoY Growth] respectively. Remember to set YoY Growth's format to percentage.
Column chart (Regional Sales): drag a Clustered column chart, axis = region_cd (or English "Region Code"), values = [Total Sales].
Line chart (Trend): drag a Line chart, axis = Date[YearMonth], values = [Total Sales] and [YTD Sales] (two lines).
Top 10 products table: drag a Table, rows = Product Master[product_name], values = [Total Sales]. Click Top N filter → choose "by Total Sales" → Top 10.
Slicer (Date filter): drag a Slicer, field = Date[YearMonth]. When someone clicks a month, the entire dashboard updates.

Adjust positions and sizes, then File → Save as Sales_Dashboard.pbix.

Step 5: Export Chart Assets (5 minutes)

The PPT needs Power BI charts, but PPT cannot directly embed a live Power BI report (Power BI Service's "Publish to web" and "Embed in PPT" are enterprise features—free users cannot use them). Safe approach:

Approach A (recommended, free): Screenshot from Power BI

After the dashboard is ready, in Power BI Desktop press Print Screen or use Win + Shift + S to screenshot. Save as regional_sales.png, trend_chart.png, top10_products.png.

Approach B (data-driven): Export key data as CSV

In Report view, hover the mouse over the top-right corner of the Top 10 Products table visual. The ... (more commands) button floats up → click ... → choose Export data → choose CSV → save as top10_products.csv. Then have the AI read this CSV to generate the PPT data cards.

Step 6: Generate the Report PPT with AI (10 minutes)

There are two paths here. Pick one based on whether you have Copilot:

Path 1: PowerPoint + Copilot (Microsoft 365 subscribers)

Open PowerPoint → create a blank presentation → click the Copilot button on the top ribbon (or the Copilot panel in the Home tab) → choose "Create a presentation from a file". In the dialog:

Attached files: upload your screenshots (regional_sales.png, trend_chart.png) + a pre-prepared Word outline (Report_Outline.docx, containing "I need an 8-page weekly sales report PPT").

Uncertainty note: Whether Copilot can directly recognize Power BI screenshots and generate chart-style slides—not guaranteed. The safe approach is to paste the screenshots into the Word outline as embedded images; Copilot can identify image positions when reading the Word document.
Prompt (type into the Copilot dialog):

Please generate an 8-page weekly sales report PPT based on my uploaded outline and images.
Style: business and minimal, color scheme: deep blue + white background.
Each slide should have no more than 5 bullet points, and titles should be in verb-object form.
Page 1 cover, page 2 key metrics, pages 3-4 regional analysis, page 5 trend,
page 6 Top 10 products, page 7 conclusions and next week's plan, page 8 Q&A.

Click "Generate". Copilot gives you a draft—but all numbers are unreliable—it is only a layout machine; it does not read your data.

Path 2: Qwen / Kimi / ChatGPT (Universal alternative)

If you do not have Microsoft 365 Copilot, use Qwen or Kimi:

Prepare the asset pack: package the screenshots + key data CSV + 5 hand-written report points into a zip and upload.
Prompt:

You are a senior sales analyst. Help me organize the materials below into an 8-page PPT outline (Markdown format).
Each page format is:
## Page N: Title
- Point 1
- Point 2
- Image suggestion: (specify which screenshot to use)

[Key data]
Total sales XXX (in 10,000 yuan), YoY +XX%, order count XXXX, average order value XXX yuan.
Top 3 products: SKU001 (XX 万), SKU002 (XX 万), SKU003 (XX 万).
Regional distribution: East China 45%, South China 30%, North China 25%.

[Screenshot list]
1. regional_sales.png (column chart)
2. trend_chart.png (line chart)
3. top10_products.png (table screenshot)

Copy the Markdown outline from the AI into PowerPoint, manually lay it out, and paste the corresponding screenshot onto each page. You can finish in 30 minutes.

Key point: Never let the AI "calculate" numbers itself—it will fabricate. All numbers must come from Power BI; the AI only does "layout + transition sentences + subheads". This is the first red line for using AI to produce reports.

4. Principles Recap

The whole pipeline is the classic "Data → Information → Knowledge → Action" four-layer model:

Data layer (Excel): raw detail, messy, error-prone, inconsistent formats.
Information layer (Power Query): clean the data into "normalized, reusable" fact tables and dimension tables. All cleanup steps become M scripts. Next time new data arrives, just "Refresh".
Knowledge layer (Power BI + DAX): model on top of the clean tables, write measures, and visualize. The essence of DAX is "dynamic computation within the filter context"—when you click "October only", every measure that filters through the Date table recalculates automatically.
Action layer (AI + PPT): translate charts and data into a story the decision-maker can actually understand. AI here only translates; it does not compute.

Once you understand these four layers, you will realize: Excel and PPT are not the focus—the middle two layers (Power Query + Power BI) and their reusability are. Once this pipeline runs through once, next week all you do is "drop in the new Excel → refresh Power Query → refresh Power BI → re-screenshot → AI regenerates the PPT". Core manual time is compressed under 10 minutes.

5. Pitfall Guide

Pitfall 1: Do Not Mix Up DAX Measures and Calculated Columns

Measure (Measure): written under New measure. Changes with the filter context, no memory cost. Use measures for all sales metrics.
Calculated Column (Calculated Column): written under New column. Computed once per row, stored in the table, takes memory. Use only when "row-level judgment" is needed (e.g., "Customer Tier = IF(Sales > 100万, 'VIP', 'Regular')").
Misuse consequence: writing "Total Sales" as a calculated column → 30,000 rows computed 30,000 times and stored in the table → file bloats and refresh slows down.

Pitfall 2: DAX Time Intelligence Functions Require a Date Table

SAMEPERIODLASTYEAR, TOTALYTD, PARALLELPERIOD, DATEADD—all of these time intelligence functions assume you have a continuous, gapless Date table. If your sales data itself has missing dates (no orders on holidays), using Sales[Date] directly as the time axis will compute incorrectly. You must build an independent Date table + mark it as a date table.

Pitfall 3: Do Not Randomly Change Power BI's Filter Direction

After the relationship is built, the cross filter direction defaults to "Single" (from Sales Detail pointing to Master). A single relationship set to "Both" will not break anything immediately; the report only errors out with "circular dependency detected / ambiguous path" when multiple relationships chain end-to-end into a loop. Keep it Single. When "bi-directional filtering" is required, use CALCULATE + CROSSFILTER to temporarily open it.

Pitfall 4: Always Manually Verify AI-Generated Numbers

Whether you use Copilot or Qwen, the AI will fabricate numbers with impressive-looking professionalism. The "Sales 1.23 billion, YoY +15.6%" you see—very likely just made up. Only let the AI do "layout + text polishing". All numbers must come from Power BI screenshots or exported CSV.

Pitfall 5: Power BI Desktop's Copilot Is Paid; Regular Users Cannot Access It

Power BI's Copilot requires Fabric capacity or Power BI Premium Per User (PPU, ~$20/month) to unlock the full feature set. The free Power BI Desktop does not even show the Copilot button. All DAX in this article is handwritten, does not depend on Copilot, and costs nothing. Copilot's main value is "auto-generate DAX drafts" and "auto-recommend charts"—for L3 users, handwriting is faster and more controllable.

6. Advanced Extensions

After this pipeline is up and running, you can deepen it in three directions:

Automated refresh: put Sales_Detail.xlsx in OneDrive / SharePoint, and configure Scheduled refresh in Power BI Service (every morning at 7 a.m.; note that scheduled refresh requires a Pro license or above — free accounts don't have it). When you open the PPT link in the morning, the data is already fresh—even "Refresh" is saved.
Connect Power Automate for auto-PPT: Power BI report saves screenshots → Power Automate triggers → call AI API to generate PPT → auto-email the boss. No one presses a button in the entire chain.
Add a Python script for forecasting: install the Python visual in Power BI, use prophet or statsmodels to run a "next-quarter sales forecast". Add the forecast chart to the second-to-last page of the PPT, so the report has foresight rather than only summarizing the past.

Next step: If you want to systematically learn DAX, recommend The Definitive Guide to DAX by Marco Russo and Alberto Ferrari. Focus on the chapters on "filter context" and "evaluation context"—understanding these two concepts will get you halfway into DAX. After that, revisit this article's CALCULATE, ALL, FILTER, and you will have an "aha" moment.

📦 Sales Data → Analysis → Report End-to-End