Master 5 Key Power Query Techniques to Clean E‑Commerce Sales Data

E-commerce export data is messy most of the time. One file may contain multiple SKU variations, inconsistent customer information, combined address fields, duplicate or null values, and scattered pricing details. If you’ve ever tried analyzing such data in Excel or Power BI, you know how quickly things get complicated. This is where Power Query becomes incredibly useful. No matter which tool you are using—Excel or Power BI—Power Query helps clean and reshape raw e-commerce data without writing complex formulas.

In this tutorial, we will learn five essential Power Query transformations for e-commerce sales data that are especially useful for online store datasets.

Unpivoting SKU Variation Columns

Many e-commerce exports store ordered products across multiple columns, such as Stock_S, SKU 1, SKU 2, SKU 3, Variant 1, Variant 2, and Variant 3, each holding a quantity. This structure is difficult to analyze because each order spreads product details across several columns. Pivot tables, DAX measures, and SUM formulas cannot aggregate effectively across this wide format. Unpivoting creates a long, normalized fact table that is ideal for analysis.

Steps:

• Load your data into Power Query

• Select the SKU columns (Stock S, M, L, XL)
• Go to the Transform tab >> choose Unpivot Columns

• Power Query converts these SKU columns into two new columns and renames them:
  • Attribute: Size
  • Value: Quantity

Use this transformation when your export stores multiple ordered products in separate columns instead of separate rows.

Splitting Shipping Address Strings into Multiple Fields

Approximately 90% of raw e-commerce files store the entire shipping address in a single string. Splitting this field enables state-level tax reporting, shipping cost optimization, regional performance analysis, and mapping.

Steps:

• Select the Shipping Address column
• Go to the Home tab >> choose Split Column >> select By Delimiter

• Based on your data, use a delimiter such as:
  • Comma
  • Hyphen
  • Line break
  • Custom separator
• For existing data, select Comma as the separator
• Click OK

• Rename the resulting columns to meaningful labels: Street, City, Postcode, and Country

Once addresses are split, you can analyze orders by city, delivery performance by zone, sales by region, and repeat customers from specific locations. This is especially useful for local delivery businesses and region-based sales reporting.

Advanced note: Not all addresses follow the same format. Some may have extra parts, missing parts, or apartment details. In these cases, you can:

• Trim spaces after splitting
• Merge selected parts back together
• Use Extract Text Before/After Delimiter
• Create custom columns for exception handling

Trimming, Cleaning, and Standardizing Data Types

In large e-commerce datasets, customer names, SKUs, email addresses, and product categories often contain leading or trailing spaces, non-printable characters, and inconsistent capitalization. Data types may also be mismatched, such as numeric values stored as text.

These small issues can cause significant problems. The same data may appear as different values, fail in merge operations, or create duplicate groups.

Cleaning the Data:

• Trim spaces:
  • Select the text columns
  • Go to Transform >> choose Format >> select Trim
  • This removes extra spaces from the start and end

• Clean hidden characters:
  • With the same columns selected,
  • Choose Format >> select Clean
  • This removes non-printable characters
• Standardize casing:
  • Go to Transform >> select Format >> choose:
  • Capitalize Each Word for names
  • Lowercase for emails if needed

Standardizing Data Types:

• Select date columns
• Go to the Home tab >> select Data Type >> choose Date

• If required, add a column:
  • Go to the Add Column tab >> expand Date
  • Choose Year, Month, Day, Quarter, Day of Week

• Select numeric columns such as Quantity and Price
• Expand the column header icon >> select Decimal Number
• Or go to the Transform tab >> select Data Type >> choose Decimal Number or Whole Number

This transformation is essential because it makes grouping, merging, filtering, and lookup operations far more reliable.

Removing Duplicates and Handling Null Values

Nulls are common in e-commerce data. Some are harmless, while others break calculations. Payment gateways and sync processes may create duplicate OrderIDs, and null quantities or prices can break totals and visuals. If nulls are not handled correctly, results can become misleading.

Removing duplicates:

• Select OrderID and OrderDate as keys (this prevents removing legitimate same-day repeat orders)
• Go to the Home tab >> select Remove Duplicates

• Remove only truly broken rows:
  • Go to the Home tab >> select Remove Blank Rows (filter out rows where OrderID is null first)

Replacing values:

• If a blank discount means no discount, replace null with 0
• Select all Quantity and Price columns
• Go to the Transform tab >> select Replace Values:
  • Value to find: null
  • Replace with: 0

Cleaning text values:

• Remove unnecessary prefixes such as Stock_
• Select the Size column
• Go to the Transform tab >> select Replace Values:
  • Value to find: Stock_
  • Replace with: (leave blank)

Now the Size values appear clean and readable.

Filling down repeated values:

• Sometimes only the first row of an order contains the OrderID or customer name
• Select the column
• Go to Transform >> select Fill >> choose Down

Important caution: Do not blindly replace every null. A missing value can indicate “not applicable,” “unknown,” or a data issue. Always understand the business meaning before replacing values.

Creating Calculated Custom Columns

From e-commerce data, you can derive revenue or margin columns. While this can be done using Excel formulas, those break on refresh. Power Query custom columns recalculate automatically and remain part of your ETL pipeline.

Net_Revenue:

• Go to the Add Column tab >> select Custom Column
• Enter a name
• Insert the following formula

[Unit_Price] * [Qty] * (1 - [Discount_Pct])

Gross_Margin:

[Net_Revenue] - ([Cost_Per_Unit] * [Qty])

Margin_Pct (%):

if [Net_Revenue] = 0 then 0 else [Gross_Margin] / [Net_Revenue]

• Set data types explicitly: Net_Revenue and Gross_Margin to Currency, Margin_Pct to Percentage

Important: Always guard division formulas. A zero in the denominator can cause errors across the entire column in Power Query and may lead to dropped rows during load. Use the pattern if [X] = 0 then null else … for any calculated ratio.

Grouping and Aggregating for High-Performance Summary Tables

Raw e-commerce files with millions of rows can slow down reports. Grouping creates efficient aggregate tables for dashboards while preserving detailed queries for drill-through analysis.

Steps:

• Go to Transform >> select Group By
• Select Category >> click Add aggregation:
  • Sum of Net_Revenue
  • Average of Margin_Pct
  • Count of Order_ID (rows)

• Name the new query Sales_Summary and keep the original as Sales_Detail for drill-through

Pro tip: In Power BI, disable “Enable load” on the detail query and only load the summary and dimension tables to improve performance.

Final step:

• Go to the Home tab >> select Close & Apply to load the data

Conclusion

You can now apply these five essential Power Query transformations to e-commerce sales data. Power Query is one of the most effective tools for cleaning e-commerce datasets because it handles both simple and complex transformations in a repeatable way. It can significantly reduce data preparation time. These are not generic cleanup steps—they directly address real-world e-commerce reporting challenges and make downstream analysis much more efficient. Once you become comfortable with these transformations, you can build a reusable Power Query template for every new marketplace export.