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.

Practice

1. What is the main purpose of importing Excel data into Power BI?

easy

A. To analyze and visualize spreadsheet data within Power BI

B. To edit Excel formulas directly in Power BI

C. To convert Excel files into Word documents

D. To send Excel data as email attachments automatically

Solution

1. Step 1: Understand Power BI's role

   Power BI is designed to analyze and visualize data from various sources, including Excel.

2. Step 2: Identify Excel data import purpose

   Importing Excel data allows users to bring spreadsheet data into Power BI for creating reports and dashboards.

3. Final Answer:

   To analyze and visualize spreadsheet data within Power BI -> Option A

4. Quick Check:

   Excel data import = Analyze and visualize [OK]

Hint: Power BI imports Excel to analyze data visually [OK]

Common Mistakes:

• Thinking Power BI edits Excel formulas
• Confusing Excel data import with file conversion
• Assuming automatic email sending from import

2. Which of the following is the correct way to start importing an Excel file into Power BI?

easy

A. Drag the Excel file into the Power BI report canvas

B. Open Excel and save as CSV before importing

C. Use the 'Export' option in Power BI to import Excel

D. Click 'Get Data' and select 'Excel' from the options

Solution

1. Step 1: Locate data import feature in Power BI

   Power BI uses the 'Get Data' button to start importing data from various sources.

2. Step 2: Select Excel as data source

   After clicking 'Get Data', selecting 'Excel' allows you to browse and import Excel files directly.

3. Final Answer:

   Click 'Get Data' and select 'Excel' from the options -> Option D

4. Quick Check:

   Import Excel = Get Data > Excel [OK]

Hint: Use 'Get Data' > 'Excel' to import files [OK]

Common Mistakes:

• Trying to drag files onto report canvas
• Confusing export with import
• Thinking CSV conversion is mandatory

3. After importing an Excel file with multiple sheets, what will Power BI show in the Navigator window?

medium

A. A list of all sheets and named ranges to select from

B. Only the first sheet automatically loaded

C. A preview of the entire Excel workbook as one table

D. An error message if multiple sheets exist

Solution

1. Step 1: Understand Navigator window role

   The Navigator window shows available tables, sheets, or named ranges from the Excel file for selection.

2. Step 2: Identify what appears for multiple sheets

   Power BI lists all sheets and named ranges so users can choose which data to load.

3. Final Answer:

   A list of all sheets and named ranges to select from -> Option A

4. Quick Check:

   Navigator = List sheets and ranges [OK]

Hint: Navigator lists all sheets and ranges for selection [OK]

Common Mistakes:

• Assuming only first sheet loads automatically
• Expecting combined preview of all sheets
• Thinking multiple sheets cause errors

4. You imported an Excel file but see incorrect data types in Power BI. What is the best way to fix this?

medium

A. Edit the Excel file and re-import without changes

B. Ignore data types; Power BI will fix them automatically

C. Use Power Query Editor to change data types before loading

D. Delete the import and start a new Power BI report

Solution

1. Step 1: Identify issue with data types

   Incorrect data types can cause wrong analysis or errors in reports.

2. Step 2: Use Power Query Editor for correction

   Power Query Editor allows changing column data types before loading data into Power BI.

3. Final Answer:

   Use Power Query Editor to change data types before loading -> Option C

4. Quick Check:

   Fix data types = Power Query Editor [OK]

Hint: Fix data types in Power Query Editor before loading [OK]

Common Mistakes:

• Assuming Power BI auto-fixes all data types
• Re-importing without correcting data types
• Deleting report instead of fixing data

5. You want your Power BI report to update automatically when the Excel file changes. What should you do?

hard

A. Manually re-import the Excel file every time it changes

B. Set up scheduled refresh in Power BI Service connected to the Excel file

C. Save the Excel file as PDF and import the PDF instead

D. Use Excel macros to push data to Power BI

Solution

1. Step 1: Understand refresh options in Power BI

   Power BI Service can refresh datasets on a schedule to keep reports updated.

2. Step 2: Connect scheduled refresh to Excel file

   Scheduling refresh ensures Power BI pulls latest data from the Excel file automatically.

3. Final Answer:

   Set up scheduled refresh in Power BI Service connected to the Excel file -> Option B

4. Quick Check:

   Auto-update = Scheduled refresh [OK]

Hint: Use scheduled refresh in Power BI Service for updates [OK]

Common Mistakes:

• Thinking manual re-import is only way
• Importing PDF instead of Excel
• Using Excel macros which don't update Power BI automatically