Web data import in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at an e-commerce company.

📋 Request: Your manager wants you to analyze the latest product prices and ratings from a competitor's website to compare with your own products.

📊 Data: You have access to a web page that lists competitor products with columns: Product Name, Price, and Customer Rating.

🎯 Deliverable: Create a Power BI report that imports the competitor's product data from the web, cleans it, and shows a table and bar chart comparing prices and ratings.

Sample Data

Product Name	Price	Customer Rating
Wireless Mouse	25.99	4.2
Bluetooth Headphones	59.99	4.5
USB-C Charger	19.99	4.0
Gaming Keyboard	89.99	4.7
Webcam HD	49.99	4.3
Portable SSD	129.99	4.6
Smartwatch	199.99	4.4
Noise Cancelling Earbuds	79.99	4.1

1

Step 1: Open Power BI Desktop and start a new report.

No formula needed.

Expected Result

A blank Power BI report is ready for data import.

2

Step 2: Import data from the competitor's web page URL.

Home tab > Get Data > Web > Enter the competitor's product page URL > Connect.

Expected Result

Power BI shows a navigator with tables found on the web page.

3

Step 3: Select the table containing Product Name, Price, and Customer Rating and load it.

Select the correct table in the navigator > Load.

Expected Result

The competitor product data is loaded into Power BI.

4

Step 4: Clean the data in Power Query Editor: ensure Price and Customer Rating columns are numbers.

Transform tab > Change data type of Price to Decimal Number and Customer Rating to Decimal Number.

Expected Result

Price and Customer Rating columns are numeric for analysis.

5

Step 5: Close and apply changes to load cleaned data into the report.

Home tab > Close & Apply.

Expected Result

Cleaned data is available in the report view.

6

Step 6: Create a table visual showing Product Name, Price, and Customer Rating.

Insert a Table visual > Add Product Name, Price, Customer Rating fields.

Expected Result

A table displays all competitor products with their prices and ratings.

7

Step 7: Create a bar chart comparing product prices.

Insert a Bar Chart visual > Axis: Product Name, Values: Price.

Expected Result

A bar chart shows product prices side by side.

8

Step 8: Create a bar chart comparing customer ratings.

Insert a Bar Chart visual > Axis: Product Name, Values: Customer Rating.

Expected Result

A bar chart shows customer ratings for each product.

9

Step 9: Format visuals for clarity: add titles, adjust colors, and ensure labels are readable.

Select visuals > Format pane > Add titles and adjust font sizes and colors for accessibility.

Expected Result

The report is clear, easy to read, and visually appealing.

Final Result

Bar Chart: Product Prices

↓

Bar Chart: Customer Ratings

✓Smartwatch is the most expensive product at $199.99.

✓USB-C Charger is the least expensive at $19.99.

✓Gaming Keyboard has the highest customer rating of 4.7.

✓Prices and ratings vary widely across products, showing opportunities to adjust pricing or improve product features.

Bonus Challenge

Add your own product data and create a combined report comparing your prices and ratings with the competitor's.

Show Hint

Import your product data as a separate table and use relationships or merge queries to combine data for side-by-side comparison visuals.

Practice

1. What is the main purpose of using Web data import in Power BI?

easy

A. To create visualizations from local Excel files

B. To bring online data into Power BI for analysis

C. To write DAX formulas for calculations

D. To design dashboards without data

Solution

1. Step 1: Understand the role of Web data import

   Web data import allows Power BI to connect to online sources and bring data into the report.

2. Step 2: Compare options with this purpose

   Options B, C, and D describe other Power BI features unrelated to web data import.

3. Final Answer:

   To bring online data into Power BI for analysis -> Option B

4. Quick Check:

   Web data import = bring online data [OK]

Hint: Web import means getting data from internet sources [OK]

Common Mistakes:

• Confusing web import with local file import
• Thinking web import creates visuals directly
• Mixing web import with DAX formula writing

2. Which of the following is the correct first step to import data from a website in Power BI?

easy

A. Create a new measure in the report view

B. Open Power Query and write a DAX formula

C. Click 'Get Data' then select 'Web' and enter the URL

D. Export data from Power BI to Excel

Solution

1. Step 1: Identify the import process

   To import web data, you start by clicking 'Get Data' and choosing 'Web' as the source.

2. Step 2: Check other options

   Options A, B, and D do not describe the correct import steps for web data.

3. Final Answer:

   Click 'Get Data' then select 'Web' and enter the URL -> Option C

4. Quick Check:

   First step for web import = Get Data > Web [OK]

Hint: Start with Get Data > Web for web imports [OK]

Common Mistakes:

• Trying to write DAX before importing data
• Confusing report view with data import steps
• Exporting data instead of importing

3. After importing data from a website URL in Power BI, which step is usually needed to prepare the data for reports?

medium

A. Transform and clean the data in Power Query Editor

B. Directly create visuals without any changes

C. Write complex DAX measures before loading data

D. Export the data back to the website

Solution

1. Step 1: Understand data preparation after import

   Imported web data often needs cleaning and transforming to be useful.

2. Step 2: Evaluate options

   Transform and clean the data in Power Query Editor correctly states using Power Query Editor to transform data. Options A, C, and D are incorrect or irrelevant.

3. Final Answer:

   Transform and clean the data in Power Query Editor -> Option A

4. Quick Check:

   Data prep after import = transform in Power Query [OK]

Hint: Always clean web data in Power Query before reporting [OK]

Common Mistakes:

• Skipping data cleaning and using raw data
• Trying to write DAX before loading data
• Exporting data instead of preparing it

4. You tried to import data from a website but got an error saying the URL is invalid. What is the most likely cause?

medium

A. The URL is missing the 'http://' or 'https://' prefix

B. Power BI does not support web data import

C. You forgot to write a DAX formula first

D. The data is already loaded in Power BI

Solution

1. Step 1: Identify common URL errors

   Web URLs must include 'http://' or 'https://' to be valid for import.

2. Step 2: Check other options

   Power BI supports web import (B wrong), DAX is unrelated (C wrong), and data loaded already doesn't cause URL errors (D wrong).

3. Final Answer:

   The URL is missing the 'http://' or 'https://' prefix -> Option A

4. Quick Check:

   URL must start with http:// or https:// [OK]

Hint: Check URL starts with http:// or https:// [OK]

Common Mistakes:

• Ignoring URL format requirements
• Thinking DAX is needed before import
• Assuming Power BI can't import web data

5. You want to create a Power BI report that updates daily with sales data from a web API. Which approach is best to ensure fresh data?

hard

A. Export Power BI data to the web API

B. Manually copy and paste data from the website daily

C. Write DAX measures to fetch data from the web

D. Use Web data import with scheduled refresh enabled

Solution

1. Step 1: Understand data refresh options

   Using Web data import with scheduled refresh allows automatic daily updates from the web API.

2. Step 2: Evaluate other options

   Manual copy-paste (B) is inefficient, DAX cannot fetch web data (C), and exporting data (A) is opposite of importing.

3. Final Answer:

   Use Web data import with scheduled refresh enabled -> Option D

4. Quick Check:

   Scheduled refresh + web import = fresh daily data [OK]

Hint: Enable scheduled refresh for automatic web data updates [OK]

Common Mistakes:

• Relying on manual data updates
• Trying to fetch web data with DAX
• Confusing export with import