Using Power Query to Prepare a Sales Dataset

In this lesson, you will apply the concepts covered so far to a simple sales dataset.

Imagine that a retail company maintains the following information in a CSV file named SalesData.csv.

Sample Raw Dataset

Assume that the source data contains the intended transactions and that each OrderID identifies a unique order in this example.

The goal is to prepare the dataset for analysis in Power BI.

Step 1: Connect to the CSV File

1. Open Power BI Desktop.
2. Select Home → Get data → Text/CSV.
3. Browse to SalesData.csv.
4. Review the data preview.
5. Select Transform Data.

The dataset will open in Power Query Editor.

Step 2: Review the Column Names

Check that the columns have meaningful names.

For this example, the existing names are already suitable:

• OrderID
• Product
• Region
• SalesAmount
• OrderDate

If the source contains unclear names, rename them before continuing.

Step 3: Assign Appropriate Data Types

Set the columns to the following types:

The choice of numeric type should reflect the precision and currency requirements of the source data.

Check that the date values are interpreted correctly and that the sales amounts are not stored as text.

Step 4: Check for Missing Values

Inspect the Product, Region, SalesAmount, and OrderDate columns for blank or null values.

If any values are missing, investigate whether the records are incomplete, whether the missing values can be recovered, or whether the records should be excluded according to the business rules.

The sample dataset above does not contain missing values, so no replacement is necessary.

Step 5: Check for Duplicate Records

Review the dataset for duplicate transactions.

Since OrderID identifies a unique order in this example, repeated OrderID values should be investigated.

Do not remove records automatically without checking whether repeated identifiers represent duplicate transactions or legitimate order details.

Step 6: Standardize Text Columns

Select the Product and Region columns.

Use the appropriate text-cleaning operations to remove unwanted leading or trailing spaces and standardize inconsistent text values where needed.

For example, "North " should be corrected to "North" if the extra space is accidental.

Step 7: Review and Apply the Transformations

Inspect the dataset to verify that:

• Column names are clear.
• Data types are appropriate.
• Dates are valid.
• Numeric values are correctly interpreted.
• Missing values have been handled appropriately.
• Duplicate records have been investigated.
• Text values are consistent.

Once the dataset is ready, select Home → Close & Apply.

Power BI will apply the transformations and load the prepared data into the model.

Step 8: Validate the Prepared Dataset

After loading, inspect the data in Power BI Desktop.

You can create a simple table visual containing Product, Region, SalesAmount, and OrderDate to verify that the values appear as expected.

For the five sample transactions, the total sales amount should be:

55,000 + 18,000 + 2,500 + 52,000 + 1,200 = 128,700

Use this total to check that the example data has been loaded and aggregated correctly.

If the result differs, investigate the source data, data types, applied filters, and aggregation settings.

This exercise demonstrates how Power Query prepares raw data for reliable reporting.