DEV Community

GAURANGSURTE
GAURANGSURTE

Posted on

Power Query Deep Dive — Transforming & Analyzing the AdventureWorks

#powerbi #dataanlysis #dax #tutorial

Power Query Deep Dive: Transforming & Analyzing the AdventureWorks Sales Dataset in Power BI

If you've ever wondered how a pile of raw CSV exports turns into a clean, business-ready Power BI dashboard, this post walks through exactly that — using Power Query's four core transformation techniques (Grouping, Pivoting/Unpivoting, Appending, and Merging) on the classic AdventureWorks retail dataset, then layering on DAX measures and a full sales-analysis report.

The dataset

AdventureWorks is a sample dataset for a fictional bicycle manufacturer: Internet Sales order lines joined to Product, Subcategory, Category, Customer, and Territory lookup tables — a textbook star schema with one fact table and several dimension tables.

Step 1 — Grouping

Grouping collapses transaction-level rows into summarized metrics. A few of the groupings I built:

  • Category → Sum of Sales Amount — total sales per product category
  • Customer → Average of Sales Amount — average spend per customer, plus a Top N filter for the top 10 customers by total sales
  • Subcategory → Sum of Order Quantity — units sold per subcategory
  • Territory → Average of Order Quantity — average basket size by region
  • Customer → Count Distinct of Order Number — orders placed per customer
  • Category → Max of Sales Amount — highest single sale per category
  • Territory → Sum of Tax Amount — total tax collected per region

Step 2 — Pivoting & Unpivoting

To prove the transformation is lossless, I built a small reference table with Category, Sales Amount, Tax Amount, and Order Quantity, then:

  1. Unpivoted the three numeric columns into a tall Attribute / Value layout
  2. Renamed them to Metric / Amount
  3. Pivoted the Metric column back on Values = Amount

The row and column counts matched the original table exactly after the round trip — confirming nothing was lost in translation.

Step 3 — Appending

I split Internet Sales into two queries by Order Date/Year (First Half and Second Half) and combined them with Append Queries → Append as New. The resulting row count matched the sum of both halves exactly, with no duplicates or schema mismatches.

Step 4 — Merging

This is where the star schema comes together:

  1. Merge FactInternetSales with DimProduct on ProductKey (Left Outer Join) → expand Product Name, Color, Standard Cost
  2. Merge the result with DimProductSubcategory → expand Subcategory Name
  3. Merge again with DimProductCategory → expand Category Name

After Close & Apply, Manage Relationships confirmed every fact-to-dimension link was Active with the correct cardinality.

DAX measures

With a clean star schema in place, six measures power the whole report:

Total Sales =
SUMX(
    'Sales Data',
    RELATED('AdventureWorks Product Lookup'[ProductPrice]) *
    'Sales Data'[OrderQuantity]
)

Total Cost =
SUMX(
    'Sales Data',
    RELATED('AdventureWorks Product Lookup'[ProductCost]) *
    'Sales Data'[OrderQuantity]
)

Total Profit = [Total Sales] - [Total Cost]

Total Orders = DISTINCTCOUNT('Sales Data'[OrderNumber])

Total Customers = DISTINCTCOUNT('Sales Data'[CustomerKey])

Total Quantity = SUM('Sales Data'[OrderQuantity])
Enter fullscreen mode Exit fullscreen mode

The results

Once the model was built, the numbers told a clear story:

Metric Value
Total Sales 24.91M
Total Cost 14.46M
Total Profit 10.46M
Total Orders 25,164
Total Customers 17K
Total Quantity Sold 84K

A few standout findings from the sales analysis:

  • Bikes dominate — the category alone drives roughly 95% of total revenue.
  • Territory 9 leads by a mile — 29.8% of total sales, about 54% ahead of the second-place territory.
  • Mountain-200 Black, 46 is the single highest-selling product/size, at ~1.24M in sales.
  • Sales peak in June (~3.0M) then fall off a cliff in July (~1.15M) before slowly recovering into December — a pattern that repeats across all three years in the data.
  • Weekday vs weekend basket size is nearly identical (~446 vs ~441 average sale), so weekday revenue is simply higher because there are more weekdays, not because people spend more on them.

Advanced tasks

On top of the core analysis, I added a few DAX/Power-Query extras:

  • Ranking product categories by total sales
  • Above-average product detection — 39 of 130 products (~30%) sit above the average of ~191,650 in cumulative sales
  • Conditional-Column customer bands — Low / Medium / High spending tiers based on the 25th/75th percentile of customer spend (17,343 customers)
  • Conditional-Column sales bands — Low / Medium / High per order line based on Sales Amount

Wrapping up

Grouping, Pivoting/Unpivoting, Appending, and Merging cover almost everything you need to turn messy multi-table exports into a trustworthy star schema — and once that's in place, a handful of DAX measures is enough to power a complete sales dashboard. If you're learning Power BI, working through all four transformations on a real dataset like AdventureWorks (rather than just one) is the fastest way to actually understand why each one exists.

The Territory

The Mountain-200 family dominates the top of the ranking, led by Mountain-200 Black, 46 at roughly 1.24M in total sales the single highest-selling product/size combination in the catalogue. Road-250 and Road-150 variants fill most of the remaining top-20 slots, with Touring-1000 models appearing further down.

The Calendar Sales

Aggregated across the full 2020–2022 date range, monthly sales climb steadily from January (~2.3M) through a June peak (~3.0M), then drop sharply in July (~1.15M) and stay low through Q3 before recovering toward year-end, closing December at roughly 2.2M. This first-half-heavy, third-quarter-dip pattern is consistent across the three years in the underlying data.

*Full write-up with all steps and screenshots: see my https://1drv.ms/w/c/70201b25f03037d4/IQCfxH5eIuXtSKltpP5bKTA2AaY4gLJKsGtUgg4PeX8eFg8?e=HTC7T2 *

Top comments (0)