Excel data import 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 analyze the monthly sales data that is stored in an Excel file. They want you to import this data into Power BI to create reports.

📊 Data: You have an Excel file with a table named 'SalesData' containing columns: Date, Region, Product, Quantity Sold, and Sales Amount.

🎯 Deliverable: Import the Excel data into Power BI and prepare it for creating sales reports.

Sample Data

Date	Region	Product	Quantity Sold	Sales Amount
2024-01-05	North	Widget A	10	500
2024-01-12	South	Widget B	5	300
2024-02-03	East	Widget A	8	400
2024-02-15	West	Widget C	12	720
2024-03-10	North	Widget B	7	420
2024-03-22	South	Widget C	9	540
2024-04-05	East	Widget A	11	550
2024-04-18	West	Widget B	6	360

1

Step 1: Open Power BI Desktop and click on 'Get Data'. Select 'Excel' as the data source.

No formula needed. Use the Power BI interface to select the Excel file.

Expected Result

Power BI opens a navigator window showing sheets and tables in the Excel file.

2

Step 2: In the navigator window, select the table named 'SalesData' and click 'Transform Data'.

No formula needed. Just select the table and transform data.

Expected Result

Power Query Editor opens showing the 'SalesData' table for editing.

3

Step 3: Verify the data types for each column in the Power Query Editor. Change 'Date' column type to Date if needed.

In Power Query Editor, select 'Date' column, then choose 'Date' data type.

Expected Result

'Date' column is correctly set as Date type, other columns have appropriate types.

4

Step 4: Close and apply the changes to load the data into Power BI report view.

Click 'Close & Apply' in Power Query Editor.

Expected Result

Data is loaded and ready for creating reports.

Final Result

Power BI Fields Pane:
---------------------
| SalesData         |
|  - Date           |
|  - Region         |
|  - Product        |
|  - Quantity Sold  |
|  - Sales Amount   |
---------------------

Report View:
Create visuals using these fields to analyze sales.

✓Excel data is successfully imported into Power BI.

✓Data types are correctly set for accurate analysis.

✓Data is ready for creating sales reports and dashboards.

Bonus Challenge

After importing, create a simple bar chart showing total Sales Amount by Region.

Show Hint

Use 'Region' as Axis and sum of 'Sales Amount' as Values in the bar chart visual.