Power BI Project on SampleSuperStore Dataset

 

Problem Statement

Retail businesses gather large volumes of data across products, customers, geographies, and logistics, but struggle to gain meaningful insights. This blog helps analyze the Sample Superstore dataset using Power BI to explore profitability, customer segments, product categories, and regional sales trends — supporting better strategic decisions.

---

📂 Dataset Description ( Download SampleSuperStore Dataset)

The Sample Superstore dataset contains 9,994 records with 13 columns, detailing:

• Geographical Info: City, State, Region

• Customer Info: Segment, Ship Mode

• Product Info: Category, Sub-Category

• Sales Metrics: Sales, Quantity, Discount, Profit

It helps understand sales trends, profit margins, segment preferences, and performance by region or category.

---

🔄 Data Transformation in Power BI (Power Query Editor)

After uploading the data via Home > Get Data > Text/CSV, click on Transform Data to enter Power Query Editor.

---

🔍 Step 1: Check and Correct Data Types

• Right-click each column ➝ Select Change Type.

• Ensure numerical columns (e.g., Sales, Profit) are decimal, and Postal Code is text.

• Only change if incorrect.

---

🔍 Step 2: Rename Columns (If Required)

• Right-click column header ➝ Choose Rename.

• Example: Rename “Sub-Category” to “SubCategory” for clarity.

---

🔍 Step 3: Drop Unwanted Columns

• If Country is always “United States”, remove it.

• Right-click ➝ Remove.

---

🔍 Step 4: Handle Missing Values

 Identify Blank Values

• Blank ≠ Null
  Filter the column → Select “(blank)”
  👉 Displays rows where the cell is empty but not technically null.

• Check for nulls using Filter dropdown on each column.

• If any:

• Replace using Transform > Replace Values or

• Remove rows using Home > Remove Rows > Remove Blank Rows

5. Identify Null Values

• In Power Query, click the column drop-down → Filter → Load More → (null)
  👉 Select “null” to display only rows with missing values in that column.

To Remove Null Values

1. Go to Power Query Editor

2. Select the column containing nulls

3. On the Home tab → Remove Rows → Remove Blank Rows
   👉 This removes rows where the selected column has null or blank values.

---

🔹 To Replace Null Values

1. Select the column

2. Go to Transform tab → Replace Values

3. In the popup:

   • Value to Find: leave blank or type null

   • Replace With: enter the value you want (e.g., 0, "Unknown", or "Not Available")

4. Click OK

 Identify Rows with Errors

• Go to Home → Keep Rows → Keep Errors
  👉 This will isolate only rows containing errors (like invalid data types or divide-by-zero).

To Remove Rows with Errors

1. Go to Power Query Editor

2. Select the column(s) where you want to check for errors

3. On the Home tab → Remove Rows → Remove Errors

👉 This will delete all rows containing errors (such as invalid data types, conversion failures, or divide-by-zero).

---

🔍 Step 6: Remove Duplicates

Identify Duplicate Rows

• Go to Power Query Editor → Home tab → Group By or Remove Rows → Keep Duplicates.
  👉 This shows only the rows that are duplicated — useful before removing them.

• Select all columns ➝ Right-click ➝ Remove Duplicates     

Remove Duplicates if your dataset has repeated rows with the same values in all columns — it helps clean the data and ensures accurate analysis.

---

🔍 Step 7: Find and Replace Values

• Go to Transform > Replace Values

• Use it for correcting spelling issues or replacing unwanted text.

Step 8: Add Calculated Column – Profit Margin

Steps:

1. Go to Add Column > Custom Column

2. Name: Profit Margin

3. Use logic: Profit divided by Sales

4. Change type to decimal.

Power Query

Profit Margin =  [Profit] / [Sales]

Step 9: Add Column – Profit Category

Steps:

1. Go to Add Column > Conditional Column

2. Name: Profit Category

3. Rules:

• >0 → “Profit”

• <0 → “Loss”

• =0 → “Break Even”

= Table.AddColumn(#"Previous Step", "Profit Category", each if [Profit] > 0 then "Profit" else if [Profit] < 0 then "Loss" else "Break Even")

Final Step: Apply Changes

Click Close & Apply to load your cleaned dataset into Power BI.

Research Questions and Visuals

RQ1: Which States Generate the Most Profit?

Visual: Bar Chart
Summarize: Sum of Profit

Steps:

1. Choose Bar Chart.

2. Drag State to Axis.

3. Drag Profit to Values.

4. Use dropdown in Values to Summarize as Sum.

5. Sort descending by Profit.

6. Format:

   • Enable Data Labels

   • Use gradient color by Profit

   • Set X-axis title to "States" and Y-axis to "Total Profit"

---

📊 RQ2: What is the Sales Performance by Sub-Category?

Visual: Clustered Column Chart
Summarize: Sum of Sales

Steps:

1. Insert Clustered Column chart.

2. Axis: Sub-Category

3. Values: Sales ➝ Summarize as Sum

4. Format:

   • Data Labels ON

   • Sort from high to low sales

   • Use distinct category colors

---

📊 RQ3: Region & Segment-wise Profit Analysis

Visual: Clustered Bar Chart
Summarize: Sum of Profit

Steps:

1. Insert Clustered Bar Chart.

2. Axis: Region

3. Legend: Segment

4. Values: Profit ➝ Summarize as Sum

5. Format:

   • Set proper axis labels

   • Enable legend at top

   • Use diverging colors for regions

---

🥧 RQ4: What is the Distribution of Orders by Ship Mode?

Visual: Pie Chart
Summarize: Count of Orders or Sum of Sales

Steps:

1. Use Pie Chart.

2. Legend: Ship Mode

3. Values: Use Sales or count of Customer ID

4. Format:

   • Show percentage data labels

   • Title: "Orders by Shipping Mode"

---

🍩 RQ5: Which Product Categories Are More Profitable?

Visual: Donut Chart
Summarize: Sum of Profit

Steps:

1. Choose Donut Chart.

2. Legend: Category

3. Values: Profit

4. Format:

   • Display total in center

   • Label slices with % and category name

---

📈 RQ6: What Are the Sales Trends by Product Category?

Visual: Line Chart
Summarize: Sum of Sales

Steps:

1. Insert Line Chart.

2. Axis: Category

3. Values: Sales ➝ Summarize as Sum

4. Legend (optional): Sub-Category

5. Format:

   • Use markers on line

   • Smooth line ON

   • Title: "Sales Trend by Category"

---

🔘 RQ7: How Does Discount Impact Profit?

Visual: Scatter Chart
Summarize: None – raw values

Steps:

1. Select Scatter Chart.

2. X-axis: Discount

3. Y-axis: Profit

4. Details: Sub-Category or Product Name

5. Format:

   • Color by Profit Category

   • Adjust transparency for overlap

---

🧱 RQ8: How Does Sales Vary by Region and Segment?

Visual: Stacked Column Chart
Summarize: Sum of Sales

Steps:

1. Insert Stacked Column Chart.

2. Axis: Region

3. Legend: Segment

4. Values: Sales ➝ Summarize as Sum

5. Format:

   • Turn on total data labels

   • Legend position: bottom

---

🧱 RQ9: Quantity Sold by Category & Sub-Category

Visual: Stacked Bar Chart
Summarize: Sum of Quantity

Steps:

1. Insert Stacked Bar Chart.

2. Axis: Sub-Category

3. Legend: Category

4. Values: Quantity ➝ Summarize as Sum

5. Format:

   • Enable data labels

   • Contrast color between categories

---

📊 RQ10: What Is the Distribution of Profit Margins?

Visual: Histogram (or bar with ranges)
Summarize: Count of Records

Steps:

1. Create groups for Profit Margin.

2. Use bar chart to show count of records in each range.

3. Format:

   • Label axes clearly

   • Color by range bucket

---

🧠 Final Summary

With Power BI:

• We cleaned and transformed the data,

• Added calculated fields like Profit Margin and Profit Category,

• Designed visual dashboards using bar, pie, donut, scatter, and line charts,

• Used summarization options like sum, count, and average for each visual,

• Gained real business insights through powerful visuals and slicers.