CSV and text file 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 last quarter's sales data which is stored in CSV and text files. You need to import these files into Power BI to create reports.

📊 Data: You have two files: 'SalesData.csv' containing sales transactions with columns Date, Product, Region, and SalesAmount; and 'ProductInfo.txt' containing product details with columns ProductID, ProductName, and Category separated by tabs.

🎯 Deliverable: Import both files into Power BI, combine the data properly, and prepare it for creating sales reports.

Sample Data

Date	Product	Region	SalesAmount
2024-01-05	P1001	North	250
2024-01-06	P1002	South	180
2024-01-07	P1003	East	300
2024-01-08	P1001	West	220
2024-01-09	P1002	North	190
2024-01-10	P1003	South	310
2024-01-11	P1001	East	260
2024-01-12	P1002	West	200

ProductID	ProductName	Category
P1001	Wireless Mouse	Accessories
P1002	USB Keyboard	Accessories
P1003	HD Monitor	Displays

1

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

No formula needed.

Expected Result

Power BI Desktop is ready for data import.

2

Step 2: Import the 'SalesData.csv' file.

Home tab > Get Data > Text/CSV > Select 'SalesData.csv' > Load

Expected Result

SalesData table is loaded with columns Date, Product, Region, SalesAmount.

3

Step 3: Import the 'ProductInfo.txt' file with tab delimiter.

Home tab > Get Data > Text/CSV > Select 'ProductInfo.txt' > In preview, set delimiter to Tab > Load

Expected Result

ProductInfo table is loaded with columns ProductID, ProductName, Category.

4

Step 4: Check data types for both tables and correct if needed.

In Power Query Editor, verify Date is date type, SalesAmount is number, ProductID and Product are text.

Expected Result

Data types are correct for analysis.

5

Step 5: Create a relationship between SalesData and ProductInfo tables on Product and ProductID columns.

Model view > Drag 'Product' from SalesData to 'ProductID' in ProductInfo

Expected Result

Relationship is active and many-to-one from SalesData to ProductInfo.

6

Step 6: Close Power Query Editor and apply changes.

Click 'Close & Apply' button

Expected Result

Data is loaded into Power BI model ready for report building.

Final Result

-----------------------------------------
| Sales Report Dashboard                 |
|---------------------------------------|
| Region | Total Sales | Top Product    |
|---------------------------------------|
| North  | $440        | Wireless Mouse |
| South  | $490        | HD Monitor     |
| East   | $560        | HD Monitor     |
| West   | $420        | Wireless Mouse |
-----------------------------------------

✓East region has the highest total sales.

✓HD Monitor is the top-selling product in South and East regions.

✓Wireless Mouse leads sales in North region.

✓Wireless Mouse leads sales in West region.

Bonus Challenge

Create a calculated column in SalesData to categorize sales as 'High' if SalesAmount > 250, else 'Low'.

Show Hint

Use Power Query Editor or DAX formula: SalesCategory = IF(SalesData[SalesAmount] > 250, "High", "Low")