Data type changes in Power BI - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants you to prepare a sales report but notices some numbers are treated as text, causing errors in calculations. You need to fix the data types to get accurate totals and averages.

📊 Data: You have a sales dataset with columns: OrderID (text), Product (text), Quantity (text), Price (text), and OrderDate (text). Quantity and Price should be numbers, and OrderDate should be a date.

🎯 Deliverable: A Power BI report with correct data types applied, showing total sales amount and average quantity sold per product.

Sample Data

OrderID	Product	Quantity	Price	OrderDate
ORD001	Notebook	10	2.5	2024-01-05
ORD002	Pen	20	1.2	2024-01-06
ORD003	Notebook	5	2.5	2024-01-07
ORD004	Marker	15	3.0	2024-01-08
ORD005	Pen	10	1.2	2024-01-09
ORD006	Notebook	8	2.5	2024-01-10
ORD007	Marker	7	3.0	2024-01-11
ORD008	Pen	12	1.2	2024-01-12

1

Step 1: Open Power BI Desktop and load the sales data table.

Use 'Get Data' to import the data from your source file.

Expected Result

Sales data appears in Power BI with all columns as text type.

2

Step 2: Go to the 'Transform Data' option to open Power Query Editor.

Click 'Transform Data' button on the Home ribbon.

Expected Result

Power Query Editor opens showing the sales data.

3

Step 3: Change the data type of 'Quantity' column from text to whole number.

Select 'Quantity' column, then choose 'Data Type' dropdown and select 'Whole Number'.

Expected Result

'Quantity' column values convert to numbers without errors.

4

Step 4: Change the data type of 'Price' column from text to decimal number.

Select 'Price' column, then choose 'Data Type' dropdown and select 'Decimal Number'.

Expected Result

'Price' column values convert to decimal numbers correctly.

5

Step 5: Change the data type of 'OrderDate' column from text to date.

Select 'OrderDate' column, then choose 'Data Type' dropdown and select 'Date'.

Expected Result

'OrderDate' column values convert to date format.

6

Step 6: Close and apply the changes to load the transformed data into Power BI.

Click 'Close & Apply' button in Power Query Editor.

Expected Result

Data loads with correct data types applied.

7

Step 7: Create a new measure to calculate total sales amount.

Total Sales = SUMX(Sales, Sales[Quantity] * Sales[Price])

Expected Result

Measure 'Total Sales' calculates the sum of Quantity times Price.

8

Step 8: Create a new measure to calculate average quantity sold per product.

Average Quantity = AVERAGE(Sales[Quantity])

Expected Result

Measure 'Average Quantity' calculates the average quantity.

9

Step 9: Build a report visual: add a table with 'Product', 'Total Sales', and 'Average Quantity' columns.

Insert Table visual, drag 'Product' field, then add 'Total Sales' and 'Average Quantity' measures.

Expected Result

Table shows each product with correct total sales and average quantity.

Final Result

-----------------------------------------
| Product  | Total Sales | Average Qty  |
-----------------------------------------
| Notebook | 57.5        | 7.67         |
| Pen      | 50.4        | 14.0         |
| Marker   | 66.0        | 11.0         |
-----------------------------------------

✓Notebook has total sales of 57.5 with an average quantity of about 7.67 units sold.

✓Pen has total sales of 50.4 with an average quantity of 14 units sold.

✓Marker leads in total sales with 66.0 and average quantity of 11 units.

Bonus Challenge

Create a line chart showing total sales over time by month.

Show Hint

Use the 'OrderDate' column (now a date type) to create a month hierarchy and plot 'Total Sales' measure on the Y-axis.