Data source and dataset in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a business analyst at a retail company.

📋 Request: Your manager wants you to prepare a report that shows sales performance by product category for the last quarter.

📊 Data: You have access to a sales data source that contains transaction details including Date, Product Category, Product Name, Quantity Sold, and Sales Amount.

🎯 Deliverable: Create a Power BI report that connects to the sales data source, loads the dataset, and displays total sales by product category for the last quarter.

Sample Data

Date	Product Category	Product Name	Quantity Sold	Sales Amount
2024-01-15	Electronics	Smartphone	10	5000
2024-01-20	Electronics	Laptop	5	7000
2024-02-05	Home Appliances	Microwave	8	1600
2024-02-18	Home Appliances	Refrigerator	3	4500
2024-03-10	Furniture	Office Chair	7	1400
2024-03-15	Furniture	Desk	4	1200
2024-03-20	Electronics	Tablet	6	1800
2024-01-25	Furniture	Bookshelf	2	600
2024-02-28	Electronics	Headphones	15	1500
2024-03-30	Home Appliances	Vacuum Cleaner	5	1250

1

Step 1: Connect Power BI Desktop to the sales data source (e.g., Excel file or database) containing the sales transactions.

Use 'Get Data' > select the appropriate source > load the sales data table.

Expected Result

Sales data table is loaded into Power BI with columns: Date, Product Category, Product Name, Quantity Sold, Sales Amount.

2

Step 2: Create a new calculated column to extract the quarter from the Date column.

Quarter = "Q" & FORMAT( 'Sales'[Date], "Q" )

Expected Result

A new column 'Quarter' is added showing values like Q1 for dates in the first quarter.

3

Step 3: Filter the dataset to include only sales from the last quarter (Q1 2024).

Apply a report-level filter where 'Quarter' equals 'Q1'.

Expected Result

Only sales records from January to March 2024 are included in the report.

4

Step 4: Create a measure to calculate total sales amount.

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

Expected Result

A measure 'Total Sales' is created that sums the Sales Amount column.

5

Step 5: Build a bar chart visualization with Product Category on the axis and Total Sales as the value.

Visual configuration: Axis = Product Category, Values = Total Sales measure.

Expected Result

Bar chart shows total sales by each product category for Q1 2024.

Final Result

Sales Performance by Product Category (Q1 2024)

| Electronics | ██████████████ 15,800 |
| Home Appliances | ████████ 7,350 |
| Furniture | █████ 3,200 |

(Bar lengths represent total sales amounts)

✓Electronics category has the highest sales in Q1 2024 with $15,800.

✓Home Appliances and Furniture have lower sales compared to Electronics.

✓This report helps focus marketing efforts on top-performing categories.

Bonus Challenge

Add a slicer to the report to allow filtering sales by month within the quarter.

Show Hint

Create a Month column using FORMAT('Sales'[Date], "MMMM") and add it as a slicer visual.

Practice

1. What is a data source in Power BI?

easy

A. The original location where data is stored before importing

B. A visual report created in Power BI

C. A chart made from data

D. A filter applied to data

Solution

1. Step 1: Understand the term 'data source'

   A data source is where data originally exists, like a database or file.

2. Step 2: Differentiate from dataset

   A dataset is the data imported into Power BI for analysis, not the original location.

3. Final Answer:

   The original location where data is stored before importing -> Option A

4. Quick Check:

   Data source = original data location [OK]

Hint: Data source is where data lives before Power BI imports it [OK]

Common Mistakes:

• Confusing data source with dataset
• Thinking data source is a report or visualization
• Mixing data source with filters

2. Which of the following is the correct way to add a new dataset in Power BI?

easy

A. Click 'Get Data' and select the data source to load data

B. Create a new report without connecting to any data

C. Use the 'Format' pane to add data

D. Right-click on a visual and select 'Add Dataset'

Solution

1. Step 1: Identify how to load data

   Power BI uses 'Get Data' to connect to data sources and load datasets.

2. Step 2: Eliminate incorrect options

   Creating reports without data or using format pane does not add datasets.

3. Final Answer:

   Click 'Get Data' and select the data source to load data -> Option A

4. Quick Check:

   Loading data = Get Data button [OK]

Hint: Use 'Get Data' to import datasets from sources [OK]

Common Mistakes:

• Trying to add data from the Format pane
• Assuming reports create datasets automatically
• Looking for dataset options in visuals

3. Consider this Power BI M code snippet to load data from a CSV file:

let
  Source = Csv.Document(File.Contents("C:\\Data\\sales.csv"),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
  ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Sales", Int64.Type}, {"Region", type text}})
in
  ChangedType

What does the Source step represent?

medium

A. A visualization of sales data

B. The dataset after changing column types

C. The original data loaded from the CSV file

D. A filter applied to the sales data

Solution

1. Step 1: Analyze the 'Source' step

   'Source' uses Csv.Document and File.Contents to load raw data from the CSV file.

2. Step 2: Understand subsequent steps

   'ChangedType' modifies data types; 'Source' is before this, so raw data.

3. Final Answer:

   The original data loaded from the CSV file -> Option C

4. Quick Check:

   Source = raw data load step [OK]

Hint: 'Source' usually means raw data load in Power Query [OK]

Common Mistakes:

• Confusing 'Source' with transformed data
• Thinking 'Source' is a visualization
• Assuming 'Source' applies filters

4. You tried to load data from an Excel file in Power BI but got an error. Which of these is the most likely cause?

medium

A. You need to format the data as a table in Power BI

B. You forgot to create a report before loading data

C. Power BI does not support Excel files

D. The file path is incorrect or file is missing

Solution

1. Step 1: Identify common errors loading Excel data

   Incorrect file path or missing file causes load errors.

2. Step 2: Eliminate incorrect reasons

   Power BI supports Excel files; reports are created after loading data; formatting in Power BI is not required to load.

3. Final Answer:

   The file path is incorrect or file is missing -> Option D

4. Quick Check:

   File path errors cause load failures [OK]

Hint: Check file path first if data load fails [OK]

Common Mistakes:

• Thinking Power BI can't load Excel files
• Assuming report creation is needed before data load
• Believing data must be pre-formatted in Power BI

5. You want to combine sales data from two different Excel files into one dataset in Power BI. What is the best approach?

hard

A. Load both files as separate datasets and create a relationship between them

B. Use Power Query to append the two tables into a single dataset

C. Load one file and manually copy data from the other into Power BI

D. Create two reports, one for each file, and merge reports later

Solution

1. Step 1: Understand combining data sources

   Appending tables in Power Query merges data into one dataset for analysis.

2. Step 2: Evaluate other options

   Creating relationships keeps data separate; manual copying is inefficient; merging reports is not practical.

3. Final Answer:

   Use Power Query to append the two tables into a single dataset -> Option B

4. Quick Check:

   Append tables in Power Query to combine datasets [OK]

Hint: Append tables in Power Query to merge datasets [OK]

Common Mistakes:

• Using relationships instead of appending for combined data
• Copy-pasting data manually
• Merging reports instead of datasets